CODEWITHSUNDEEP
vbaexcel
alwaysLearningABC
alwaysLearningABC

Reputation: 314

Change the Excel.Button.Caption property inside of a loop?

I have the code below attached to a button in Excel. When the button is clicked, I would like to display a countdown by changing the Caption of the button to "5... 4... 3... 2... 1... 0" and waiting 1 second between each change. The "i" variable holds the countdown second.

The cell to which I'm writing "i" displays the countdown correctly, but the button caption only changes when it gets to "0". It doesn't display the intermediate values.

Why does the button text not change until after the loop is finished?

Sub Timer()
    Const COUNTDOWN As Integer = 5

    Dim testButton As Excel.Button
    Set testButton = Application.ActiveSheet.Buttons("TestButton")
    testButton.Select

    For i = COUNTDOWN To 0 Step -1
        ActiveCell.FormulaR1C1 = i
        testButton.Caption = i
        Call Application.Wait(Now + TimeValue("00:00:01"))
   Next i
End Sub

Upvotes: 0

Views: 452

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Likely some repaint isn't happening, whatever the reason is (assuming Application.ScreenUpdating isn't turned off... which shouldn't be the case, since the cell value does get updated).

Add a DoEvents call after the Caption assignment.

For i = COUNTDOWN To 0 Step -1
    ActiveCell.FormulaR1C1 = i
    testButton.Caption = i
    DoEvents '<~ let Excel process what just happened
    Application.Wait Now + TimeValue("00:00:01")
Next i

That will also let Excel process any pending events and Windows messages.

Upvotes: 1

Related Questions