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.

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

LVL 10
FamousMortimerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yogi4lifeCommented:
I think DoEvents might help you.
        http://support.microsoft.com/kb/118468

And you also might have use for:

    Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
         http://msdn.microsoft.com/en-us/library/aa195809(office.11).aspx

0
curtiswongCommented:
Try the attached file
Book1a.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FamousMortimerAuthor Commented:
The DoEvents and Button.Activate was exactly what I was missing
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.