Ribbon force refresh-CodePudding

I'm working on a custom VSTO ribbon in Excel, more specifically a group that would look like below:

Using the code below, I want to turn off/on things like Screen Updating, Calculation, Enable events.

This is not how I'm going to use this, I will use that functionality in individual macros, what I want to do is signal the status of each of these items in the toggle buttons (DA, EE, SA, C), as I did with updateCheckboxes. I want to do this in case the user ran into an unhandled debug error, and the macro did not reach the end where the status is restored.

My problem comes when .ScreenUpdating = False, because this no longer allows the ribbon to refresh itself, so the toggle buttons do not show the updated values.

Is there any way to force refresh the ribbon, even if screen updating is set to false?

   Private Sub Button6_Click(sender As Object, e As RibbonControlEventArgs) Handles 
    Button6.Click
        With Globals.ThisWorkbook.Application
            '.ScreenUpdating = False
            .Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
            .EnableEvents = False
            .DisplayAlerts = False
        End With
        updateCheckboxes()
    End Sub

    Private Sub Button7_Click(sender As Object, e As RibbonControlEventArgs) Handles Button7.Click
        With Globals.ThisWorkbook.Application
            .DisplayAlerts = True
            .EnableEvents = True
            .Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic
            '.ScreenUpdating = True
        End With
        updateCheckboxes()
    End Sub

    Private Sub updateCheckboxes()
        With Globals.ThisWorkbook.Application
            Me.tglDA.Checked = (.DisplayAlerts = True)
            Me.tglEE.Checked = (.EnableEvents = True)
            Me.tglSA.Checked = (.ScreenUpdating = True)
            Me.tglC.Checked = (.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic)
        End With
        Application.DoEvents()
    End Sub

CodePudding user response：

You need to call Globals.ThisAddIn.RibbonUI?.Invalidate() to cause Excel to fire all the getVisible/getEnabled/getLabel callbacks on ribbon controls, at which point you can return the updated control labels.

CodePudding user response：

I don't think the Ribbon UI (aka Fluent UI) is calling ribbon callbacks when the ScreenUpdating property is set to false. However, as Dmitry suggested, you may try using the Invalidate method of the IRibbonUI interface. An instance of the IRibbonUI interface is passed to the Load callback:

Dim MyRibbon As IRibbonUI 
 
Sub MyAddInInitialize(Ribbon As IRibbonUI) 
 Set MyRibbon = Ribbon 
End Sub 
 
Sub myFunction() 
 MyRibbon.Invalidate() ' Invalidates the caches of all of this add-in's controls 
End Sub

Don't forget to specify the OnLoad callback in the ribbon XML:

<customUI … onl oad="MyAddinInitialize" …>

Read more about the Fluent UI in the following series of articles:

Anyway, I'd recommend re-designing the application without relying on anything except returning back default values, so you can start a timer or set a timeout and etc. to stop a long running or faulty task.