×

Refresh Ribbon UI In Your Custom Excel Add-in

By Chris Newman •  Updated: 09/21/21 •  6 min read
VBA code snippet to refresh Ribbon UI in your custom Excel Add-in

What This VBA Code Does

There may be times when you need to refresh the user interface (UI) of your custom Excel add-in after Excel launches your add-in or while your users are interacting with your add-in.

The below code will show you how to properly store your add-in's ribbon object and reload it whenever you call the macro “RefreshRibbon”. Note, this code works for both 32-bit and 64-bit Excel (hence the constant testing for VBA7)

VBA Code:

This first subroutine is the macro the add-in calls via the XML code upon the RibbonLoad event (when the add-in’s Ribbon UI is first loaded after launching in Excel). This code will store the Ribbon object to a public variable called myRibbon.

In this code, we also save a unique ID that points to the ribbon object to a named range within the add-in file called RibbonID. This is important because if you edit your VBA code while testing your add-in, your public variable (myRibbon) will lose its value and you cannot refresh the Ribbon UI until you restart Excel (very annoying!).

Public myRibbon As IRibbonUI

'Used to store RibbonUI ID to Registry
  #If VBA7 Then
      Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
  #Else
      Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
  #End If


Sub RibbonLoaded_MyCustomAddin(ribbon As IRibbonUI)
'PURPOSE: Run code when Ribbon loads the UI to store Ribbon Object's Pointer ID code

'Handle variable declaration if 32-bit or 64-bit Excel
#If VBA7 Then
  Dim StoreRibbonPointer As LongPtr
#Else
  Dim StoreRibbonPointer As Long
#End If

'Store Ribbon Object to Public variable
  Set myRibbon = ribbon
  
'Store pointer to IRibbonUI in a Named Range within add-in file
  StoreRibbonPointer = ObjPtr(ribbon)
  ThisWorkbook.Names.Add Name:="RibbonID", RefersTo:=StoreRibbonPointer
   
End Sub

The next macro will actually perform the refreshing of the Ribbon object. You’ll notice there is an If Statement to handle if the public myRibbon variable was emptied (ie you made changes in the VBA code). In this case, it directs the variable back to the Ribbon object by calling the Ribbon ID stored in the Named Range.

Sub RefreshRibbon()
'PURPOSE: Refresh Ribbon UI

On Error GoTo RestartExcel
  If myRibbon Is Nothing Then
    Set myRibbon = GetRibbon(Replace(ThisWorkbook.Names("RibbonID").RefersTo, "=", ""))
  End If
  
  'Redo Ribbon Load
    myRibbon.Invalidate
On Error GoTo 0

Exit Sub

'ERROR MESSAGES:
RestartExcel:
MsgBox "Please restart Excel for Ribbon UI changes to take affect", , "Ribbon UI Refresh Failed"

End Sub

The above macro uses a function called GetRibbon to assist with re-populating the myRibbon variable (in the event it is warranted).

#If VBA7 Then
  Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
  Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If

  Dim objRibbon As Object
  
  CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
  Set GetRibbon = objRibbon
  Set objRibbon = Nothing
  
End Function

Finally, you can refresh your Ribbon UI anytime throughout your code by simply writing the below line within your macros:

Call RefreshRibbon

Modify The XML Code

On the XML side, we will need to trigger the macro that stores the instance of the Ribbon (ie RibbonLoadedmyCustomAddin). All we need to do is modify the customUI node and add an onLoad attribute. This essentially works like the onAction attribute when you call VBA macros after a Ribbon button is clicked, however, this calls the macro when the Ribbon first gets loaded (when the Excel application gets opened). Notice how we use the same macro name “RibbonLoaded_MyCustomAddin” as we used in the VBA portion of this article.

XML Code Adjustment:

<customUI onLoad="RibbonLoaded_MyCustomAddin" xmlns="http://schemas.microsoft.com/office/2006/01/customui">

Using VBA Code Found On The Internet

Now that you’ve found some VBA code that could potentially solve your Excel automation problem, what do you do with it? If you don’t necessarily want to learn how to code VBA and are just looking for the fastest way to implement this code into your spreadsheet, I wrote an article (with video) that explains how to get the VBA code you’ve found running on your spreadsheet.

Getting Started Automating Excel

Are you new to VBA and not sure where to begin? Check out my quickstart guide to learning VBA. This article won’t overwhelm you with fancy coding jargon, as it provides you with a simplistic and straightforward approach to the basic things I wish I knew when trying to teach myself how to automate tasks in Excel with VBA Macros.

Also, if you haven’t checked out Excel’s latest automation feature called Power Query, I have put together a beginner’s guide for automating with Excel’s Power Query feature as well! This little-known built-in Excel feature allows you to merge and clean data automatically with little to no coding!

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to get quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee that 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.