Search code examples
excellabeluserform

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

Solution

  • 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