• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1542
  • Last Modified:

Update (Repaint) Activex command button control on worksheet with VBA (in Do...Loop)

Hi Everyone,

I am writing a program that will loop all day and show a live data feed from our database. I am using a button to display the status code. The button is on a worksheet (not a form).
Based on the status code, the button.caption, .forecolor, .backcolor will be different. I would like the button properties to refresh/repaint at the beginning of each loop. Right now they only refresh after the code is stopped. Is there a method to force it to refresh while code is being executed?

Below is the code that controls the button, let me know if you need more information.

The code will run in a loop because it is querying from the database every few minutes.  I also want the large button to flash on and off so it is obvious and can be seen from quite a distance.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub LiveDataTest()
    For i = 1 To 2
        With shtTimeLine
            For l = 1 To 10
                .btnStatus.Visible = True
                Sleep 1000
                .btnStatus.Visible = False
                Sleep 1000
            Next l
            .btnStatus.Visible = True
        End With
    Next i
End Sub

Sub UpdateButton()

    Dim clrCode1 As String, clrCode2 As String, clrText As String
    Dim CurrentCode  As Integer, CodeDesc As String

        CurrentCode = shtData.Range("M65536").End(xlUp).Value
        CodeDesc = Application.WorksheetFunction.VLookup(CurrentCode, Range("WorkCodes"), 2, False)
        If CurrentCode = 2 Then CodeDesc = CodeDesc & "NING Job #: " & shtData.Range("L65536").End(xlUp).Value
        If CurrentCode = 1 Or CurrentCode = 3 Then
            clrCode1 = &H80FF&
            clrCode2 = &H40C0&
            clrText = &HFFFF&
        ElseIf CurrentCode = 2 Then
            clrCode1 = &HC000&
            clrCode2 = &H8000&
            clrText = &HC0FFC0
            clrCode1 = &HFF&
            clrCode2 = &HC0&
            clrText = &HC0C0&
        End If
        With shtTimeLine
            .btnStatus.BackColor = clrCode1
            .btnStatus.ForeColor = clrText
            .btnStatus.Caption = CodeDesc
        End With
End Sub

Open in new window

2 Solutions
I think DoEvents might help you.

And you also might have use for:

    Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)

Try the attached file
FamousMortimerAuthor Commented:
The DoEvents and Button.Activate was exactly what I was missing
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now