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
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