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

Question

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

I have tested this in Excel 2016 and it is working on both countdowns of the ActiveCell and also the testButton caption. They are displaying each number as it counts down. What version of excel are you using? — 5202456
– 5202456, Commented Jun 20, 2018 at 15:05

Mathieu Guindon · Accepted Answer · 2018-06-20 20:18:55Z

1

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.

edited Jun 20, 2018 at 20:18

answered Jun 20, 2018 at 15:26

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alwaysLearningABC Over a year ago

The DoEvents call doesn't make a difference for me. Likely it is some Excel for Mac issue.

Mathieu Guindon Over a year ago

Specifying it was a Mac from the start would have helped... Mac and ActiveX don't really play well. Replace the button with a shape (you'll get a much nicer looking button anyway), and assign it to a macro instead of handling its Click event. You'll be able to access it through the Shapes collection of your worksheet; declare a Shape object to access its properties, including its text content.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related