Check Out Our Courses!Learn More

How To Refresh The Ribbon On Command

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:

  • Hiding/unhiding buttons based on a user-initiated trigger
  • Changing button icons
  • Using certain controls, such as a checkbox

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.

VBA Adjustments

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.

XML Adjustments

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">