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. The below code will show you how to properly store your add-ins ribbon object and reload it whenever your 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 are also saving 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 are editing your VBA code while testing your add-in, your public variable (myRibbon) will lose its value and you will not be able to refresh the Ribbon UI until you restart Excel (very annoying!).
Public myRibbon As IRibbonUI
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 SubThe 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 that was stored in the named range.
Sub RefreshRibbon()
'PURPOSE: Refresh Ribbon UI
Dim myRibbon As Object
On Error GoTo RestartExcel
If myRibbon Is Nothing Then
Set myRibbon = GetRibbon(Replace(ThisWorkbook.Names("TSGRibbonID").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 SubThe 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 FunctionFinally, you can refresh your Ribbon UI anytime throughout your code by simply writing the below line within your macros:
Call RefreshRibbonModify 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
![Which Excel Versions Have XLOOKUP Function? [Solved]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/DoIHaveXLOOKUP.png)
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.