CODEWITHSUNDEEP
excellabeluserform
10101
10101

Reputation: 2412

Excel VBA real time UserForm Labels from Workbook cells

I have userform called "DisplaySummaryForm" that meants to display live information about project being calculated. I have Labels to display values from worksheet cells. Now I have to reopen UserForm all the time toget my values updated. How they can be updated all the time? So they are so called "rela time" in opened UserForm?

Button for opening UserForm:

Sub DisplaySummary()

DisplaySummaryForm.Show vbModless

End Sub

UserForm code:

Private Sub CommandButton1_Click()

Unload Me

End Sub
Private Sub UserForm_Initialize()

Controls("Label11").Caption = ThisWorkbook.Sheets("MAIN").Range("D11").value
Controls("Label12").Caption = ThisWorkbook.Sheets("MAIN").Range("D14").value

Me.TextBox2.value = ThisWorkbook.Sheets("Price calculation").Range("I148").value

Controls("Label14").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label15").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label18").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label16").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label17").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label20").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
Controls("Label22").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
End Sub

Upvotes: 0

Views: 921

Answers (2)

10101
10101

Reputation: 2412

Thanks to everyone. I came up with this solution:

Private Sub Worksheet_Calculate()
    Dim KeyCell1 As Range
    Dim KeyCell2 As Range
    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCell1 = Range("Q148")
    Set KeyCell2 = Range("Q149")
    Set KeyCell3 = Range("Q150")
    Set KeyCell4 = Range("Q151")
    Set KeyCell5 = Range("Q152")
    Set KeyCell6 = Range("Q156")
        ' Display a message when one of the designated cells has been
        ' changed.
        DisplaySummaryForm.Controls("Label14").Caption = Format(KeyCell1.Value, "#,##0.00")
        DisplaySummaryForm.Controls("Label15").Caption = Format(KeyCell2.Value, "#,##0.00")
        DisplaySummaryForm.Controls("Label16").Caption = Format(KeyCell3.Value, "#,##0.00")
        DisplaySummaryForm.Controls("Label17").Caption = Format(KeyCell4.Value, "#,##0.00")
        DisplaySummaryForm.Controls("Label18").Caption = Format(KeyCell5.Value, "#,##0.00")
        DisplaySummaryForm.Controls("Label20").Caption = Format(KeyCell6.Value, "#,##0.00")
End Sub

Upvotes: 1

Dhamo
Dhamo

Reputation: 1251

I believe there is no such option in Excel to auto update the values in Userforms but you can unload and load the DisplaySummaryForm when a cells being updated using the below code with slight modification as needed. 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("Q148")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
       Is Nothing Then

    ' Display a message when one of the designated cells has been 
    ' changed.
    ' Place your code here.
    MsgBox "Cell " & Target.Address & " has changed."
End If
End Sub

Reference for the above code

Upvotes: 1

Related Questions