There may be instances in your add-in’s user experience where you might want to reload/refresh your Ribbon UI. Some examples of why you might need this to occur include:
To give your add-in the ability to refresh the Ribbon UI anytime while Excel is opened, you will need to incorporate some VBA and XML code adjustments. Below I will describe both in detail.
On the VBA side, you will need to paste the in the following snippet of code. I recommend placing this snippet at the VERY TOP of the VBA module storing all your Ribbon-related code.
The name of the macro in my example is RibbonLoaded_myAddin. This can be renamed to anything you like, but it will need to match what is used in your XML code (which we will get to later on).
Public myRibbon As IRibbonUI Sub RibbonLoaded_myAddin(ribbon As IRibbonUI) 'PURPOSE: Store ribbon instance to a variable upon loading Set myRibbon = ribbon End Sub
The second piece of VBA code we need to add is a macro that actually refreshes the Ribbon. The command used is “Invalidate” and that will trigger a specific Ribbon tab to reload itself.
Sub RefreshRibbon()
'PURPOSE: Trigger a refresh of the Ribbon UI
On Error GoTo RestartExcel
myRibbon.Invalidate
On Error GoTo 0
Exit Sub
RestartExcel:
MsgBox "Please restart Excel for Ribbon UI changes to take effect", _
vbCritical, "Ribbon UI Refresh Failed"
End Sub
Once you have these two macros setup, you can simply use the VBA command Call RefreshRibbon within your macros whenever you need to refresh the Ribbon UI.
On the XML side, we will need to trigger the macro that stores the instance of the Ribbon (ie RibbonLoaded_myAddin). All we need to do is modify the customUI node and add an onLoad attribute. This essentially works like the onAction attribute we have been using to call macros for buttons, but this calls the macro when the Ribbon first gets loaded (when the Excel application gets opened). Notice how we use the same macro name as we used in the VBA portion of this article.
<customUI onLoad="RibbonLoaded_myAddin" xmlns="http://schemas.microsoft.com/office/2006/01/customui">