Excel VBA Ribbon getEnabled not called when code running

Question

I have an Excel VBA macro that runs for a long time (potentially for days, it performs data acquisition). It was originally written for Excel 2003 and has custom toolbars and menus. I recently updated it to use a ribbon interface using RibbonXML.

When the macro is running I want to disable some interface elements (such as "start test"), and enable others (such as the "stop test" button.)

The problem I have is that calls to ribbon.invalidate are only processed after the macro code has run to completion.

You can see this effect quite easily with a simple test program

Sub test()

ribbon.Invalidate
DoEvents
Sleep (5000)

End Sub

A debug.print in the ribbon "getEnabled" callback will be seen to only be actioned at the end of the 5 second sleep.

Is there any way to force a ribbon.Invalidate to be activated there and then?

:: Edit 1 ::

I have created a small demo workbook to ilustrate the issue: http://www.bodgesoc.org/Button_Demo.xlsm

:: Edit 2 ::

A member of a different forum found a solution, though it is a slightly ugly one. I guess this can now be marked as "answered" but a more elegant solution would be appreciated.

Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",True)"
Application.ScreenUpdating = True

Dick Kusleika Dick Kusleika · Accepted Answer · 2014-03-09T21:51:01

I don't know if this is any less ugly than what you have, but...

'Callback for Run onAction
Sub Run_r(control As IRibbonControl)

    SetRunning "Run_r_procedure"
    DoEvents

End Sub

Sub Run_r_procedure()
    Dim T As Single

    Sheet1.Range("A10").Value = "Run pressed, button states changed, and ribbon invalidated. Waiting 5 seconds in loop"
    T = Timer
    Do While Timer - T < 5
        DoEvents
    Loop
    Sheet1.Range("A10") = ""

End Sub

And then in SetRunning

Sub SetRunning(ByVal ProcToRun As String)

    Sheet1.Range("B1") = "Disabled"
    Sheet1.Range("B2") = "Disabled"
    Sheet1.Range("B3") = "Enabled"
    Sheet1.Range("B4") = "Enabled"
    Sheet1.Range("B5") = "Enabled"
    Sheet1.Range("B6") = "Enabled"
    Sheet1.Range("B7") = "Enabled"

    myRibbon.Invalidate
    myRibbon.InvalidateControl ("Run")

    Application.OnTime Now, ProcToRun

End Sub

So you have to have two procedures per callback - the callback and then whatever SetRunning will call that does the actual work. The code is just as ugly, but the UI is little less strange looking to the user.

Excel VBA Ribbon getEnabled not called when code running

1 Answers