dcsimg
Results 1 to 5 of 5

Thread: Excel VBA real time UserForm Labels from Workbook cells

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Excel VBA real time UserForm Labels from Workbook cells

    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:

    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.
    Last edited by mrwad; Oct 30th, 2018 at 10:15 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,870

    Re: Excel VBA real time UserForm Labels from Workbook cells

    you could use a timer with an interval to suit your usage

    in a module
    Code:
    Public Declare Function SetTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long, _
    ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    
    Public Declare Function KillTimer Lib "user32" ( _
    ByVal HWnd As Long, ByVal nIDEvent As Long) As Long
    
    Public TimerID As Long, TimerSeconds As Single, tim As Boolean
    Dim Counter As Long
    Dim countdown As Double
    
    
    '~~> Start Timer
    Sub StartTimer()
        '~~ Set the timer for 1 second
        TimerSeconds = 1  '  change  this value to adjust time interval, can be less than 1 as the timer woks to milliseconds
        TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc)
    End Sub
    
    '~~> End Timer
    Sub EndTimer()
        On Error Resume Next
        KillTimer 0&, TimerID
    End Sub
    
    Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, _
    ByVal nIDEvent As Long, ByVal dwTimer As Long)
        '~~> Update value in Sheet 1
        userform1.Controls("Label14").Caption = ThisWorkbook.Sheets("Price calculation").Range("Q148").value
       '  update whatever labels you require here, else call some other procedure that does
       '  change form name to suit
    
    End Sub
    you can call starttimer when you initialize the userform, or at any time as required, make sure to call endtimer when not using
    the use of intervals less than 1 second may cause excel to lag, you will have to test to see what intervals work best for you

    this code has been used for many tests and may contain unused variables, comments etc, just modify for your application
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: Excel VBA real time UserForm Labels from Workbook cells

    Quote Originally Posted by mrwad View Post
    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.
    Use the "Calculate"-Event of the Worksheet-Object
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2018
    Posts
    15

    Re: Excel VBA real time UserForm Labels from Workbook cells

    Thanks to everyone. I came up with this solution.

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

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,528

    Re: Excel VBA real time UserForm Labels from Workbook cells

    Note: The Calculate-Event fires only for cells changing due to a formula contained within.
    "Manually" changed cells don't fire that event
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width