I have userform called "DisplaySummaryForm" that meant 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 to get my values updated or press Refresh button on userform. How they can be updated all the time? So they are so called "real time" in opened UserForm?
Button for opening UserForm:
UserForm code:
I know I can use something like:
But the problem is that I am not updating my cell Q148 "manually" but by formula. In cell Q148 I have something like =A1+A6+A7+A8*23 etc. Above Macro does not work in this case.
Button for opening UserForm:
Code:
Sub DisplaySummary()
DisplaySummaryForm.Show vbModless
End Sub
UserForm code:
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
I know I can use something like:
Code:
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
But the problem is that I am not updating my cell Q148 "manually" but by formula. In cell Q148 I have something like =A1+A6+A7+A8*23 etc. Above Macro does not work in this case.