CODEWITHSUNDEEP
excelvba
10101
10101

Reputation: 2412

Update userform labels Worksheet_Calculate and each time userform is opened

I have userform where I have labels which are updated every time I am changing something on my worksheet "Price calculation". Everything seems to work fine except situation when I am closing userform and then open it again. All labels are empty then. If I go to my worksheet "Price calculation" and make changes there while userform is opened I am getting values to userform again. How can I update labels on userform opening also?

Here is my code for worksheet "Price calculation":

Private Sub Worksheet_Calculate()

Dim Ws As Worksheet

Set Ws = ThisWorkbook.Sheets("Price calculation")

Summary.Controls("Label630").Caption = Ws.Range("I1850").Value
Summary.Controls("Label635").Caption = Ws.Range("I1850").Value
Summary.Controls("Label634").Caption = Ws.Range("I1854").Value
Summary.Controls("Label633").Caption = Ws.Range("I1855").Value
Summary.Controls("Label632").Caption = Ws.Range("I1856").Value
Summary.Controls("Label631").Caption = Ws.Range("I1860").Value

End Sub

Userform opens:

Sub DisplaySummary()

Summary.Show (vbModeless)

End Sub

Upvotes: 1

Views: 56

Answers (1)

DDV
DDV

Reputation: 2395

Using the Prive Sub Userform_Initialize() you set the labels you want to when they userform opens. Something like below:

Private Sub Userform_Initialize()

   Dim Ws As Worksheet

   Set Ws = ThisWorkbook.Sheets("Price calculation")

   Me.Controls("Label630").Caption = Ws.Range("I1850").Value
   Me.Controls("Label635").Caption = Ws.Range("I1850").Value
   Me.Controls("Label634").Caption = Ws.Range("I1854").Value
   Me.Controls("Label633").Caption = Ws.Range("I1855").Value
   Me.Controls("Label632").Caption = Ws.Range("I1856").Value
   Me.Controls("Label631").Caption = Ws.Range("I1860").Value

   Set Ws = Nothing

End Sub

Upvotes: 2

Related Questions