After working hard to create and implement a custom ribbon tab for your Office© application it is annoying to find it is not as robust as it should be. This problem may show up as an Error 91 (Object variable not set) or simply the tab not loading in the ribbon. If you had a working custom ribbon tab and now it starts throwing
All the credit for this fix should go to Rory Archibald who came up with it and shared it in a forum post here. Another implementation and a great example workbook
First, you will need to save the memory pointer reference for your ribbon. I put mine in a “hidden” column which also serves as a spot to store other dynamically created numbers which are used in the worksheet’s calculations. You could also use a custom document property of the workbook. It’s your choice, in this case, the unused cell is easy. The following code shows how this is done…an explanation
' Force explicit variable declaration Option Explicit Public estRibbonUI As IRibbonUI Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long) '*************************************** ' Start of the Ribbon code * '*************************************** Public Sub OnLoad_EstimatorTab(ribbon As IRibbonUI) ' Set the estRibbonUI to a workbook property for later use Set estRibbonUI = ribbon ' set the memory pointer Sheet1.Range("T14").value = ObjPtr(ribbon) ' open the Estimator tab at startup estRibbonUI.ActivateTab ControlID:="EstimatorTab" End Sub
There is more code in the example then you need to understand. However, it is better to see the required statements in their native surroundings. This sample shows my workbook’s OnLoad sub which is referenced in the ribbon XML. It runs when the ribbon initiates and displays your custom tab.
- First, you will need to reference a windows library, “kernel32” with the Public Declare statement. This library will give you access to the function “ObjPtr”…
- Now that you have the ability to use the function it can be used to save the pointer from the IRibbonUI. In this case, we call “ObjPtr(ribbon)”. Where “ribbon’ is another variable name for the IRibbonUI (passed as an argument ribbon As IRibbonUI.)
- This work is done in this line: Sheet1.Range(“T14”).value = ObjPtr(ribbon), as the function is called with the IRibbonUI passed to it. Now cell T14 contains a Long number representing the place in memory where the IRibbonUI belongs.
Now the real work begins
The problems that we see with our custom ribbon (IRibbonUI) are they “lose” track of where it is in memory. It does become an Object variable which isn’t set. With the pointer to the IRibbonUI saved we can retrieve it when our ribbon gets “Lost.” The following code shows how I did this:
Public Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object ' retrieve the ribbon memory pointer from the holding cell, T14 Dim objRibbon As Object CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer) Set GetRibbon = objRibbon Set objRibbon = Nothing End Function Public Sub RefreshRibbon() ' check and fix the ribbon On Error GoTo errHandler If estRibbonUI Is Nothing Then If Not IsEmpty(Sheet1.Range("T14").value) Then Set estRibbonUI = GetRibbon(Sheet1.Range("T14").value) estRibbonUI.Invalidate End If Else estRibbonUI.Invalidate End If errHandler: If Err.Number > 0 Then If Err.Number = 91 Then MsgBox "An Excel Ribbon menu error has occurred which can be fixed by saving your estimate, closing it, closing Excel, and then " _ & "reopening Excel and your saved estimate. Sorry for the inconvenience!", vbInformation, "Excel Ribbon error..." Resume Next End If End If End Sub
In the previous code, the function GetRibbon is called by the sub RefreshRibbon. The error handler at the end checks for error 91 which is an error that occurs when you have ribbon issues such as discussed here. How this works…
- GetRibbon retrieves the value which was previously saved (cell T14) passes it back to the calling statement.
- Then the IRibbonUI is reset to this old value which simply returns it to the previous (correct) pointer. It copies over a false or missing value with the correct one.
- Then the ribbon Invalidate command is issued and the ribbon should be back to normal, unless…
- …we still have the error 91. Then a message is displayed telling the user to save their work and restart Excel. However, with this fix in place, this is seldom the case anymore. The following code shows how the RefreshRibbon sub can be used.
' Adjust the Ribbon checkboxes and labels If Not estRibbonUI Is Nothing Then estRibbonUI.Invalidate Else Call RefreshRibbon End If
I use the previous code at the end of the Worksheet_Activate and Change events and whenever a form makes a change to the worksheets or closes. It merely says if the ribbon object is not set run the RefreshRibbon sub, otherwise refresh it with the Invalidate command. This code keeps things running smooth. I have yet to resort to restarting Excel because of the ribbon losing its way. Except when I am coding and something crashes. But that is the way things are when developing software…
I have successfully used this method for some time in a spreadsheet used in our organization. We’ve recently started seeing issues, however, when multiple versions of the spreadsheet are used on the same PC. What is happening is that, when both sheets are started up at the same time, the OnLoad routine from one workbook is being called by both ribbons, so the address for only one ribbon is being stored.
I tried making the OnLoad function private, but that didn’t help.
The options to fix this problem that I can think of are:
(1) Make sure workbooks are loaded one at a time. (Hard to enforce.)
(2) Make the OnLoad function name unique. (Should work, but hard to do with many versions of the spreadsheet in use.)
(3) Find a way to grab the name of the workbook that is loading the ribbon, and use that in the OnLoad function to save the address in the correct workbook. (Likely the best solution, but I haven’t found a way to get the spreadsheet name from the IRibbonUI object in the OnLoad function.)
Is there another option that might work?
I had a really long reply which I thought was very informative, but I clicked the wrong thingy and it disappeared, sorry. Let me try again:
I always use unique names for my IRibbonUI. For example, I use, “Public estRibbonUI As IRibbonUI” for an estimating application.
I would try to open the workbook in its own instance of Excel. I haven’t tried this except by opening a workbook from within another workbook, but it might work. Then there is always the ActiveWorkbook.Count method that could prevent loading if there are already other workbooks loaded.
I have always had problems trying to modify the built-in toolbars as the changes seem to “stick” and affect the other workbooks. But I do use code like:
‘ Hide or show the worksheet tab context menu…hide it if the workbook is not protected
.CommandBars(“Ply”).enabled = True
This reenables a toolbar –>https://stackoverflow.com/questions/24540929/commandbarsply-wont-reset-on-workbook-beforeclose-vba-excel
Not really sure if this helps?
Great post, I am trying to implement this in a Word document and tried having the memory pointer stored in a public Long called “memoryPointer”, is this not a valid approach? It gets as far as the actual re-linking of the object but then genrates the error 91 as soon as I run the next myRibbon.Invalidate:
Public memoryPointer As Long
Set myRibbon = GetRibbon(memoryPointer)
Thanks so much
Have you tried using the data type LngPtr?
See this –>https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/longptr-data-type
I tried LongPtr and it behaved the same. However, I tried the approach you recommended with the custom document properties. I created a custom property of type “msoPropertyTypeNumber” and set the value to memoryPointer (as a LongPtr):
If myRibbon Is Nothing Then
MsgBox “attempting to handle memory reference loss”
If Not IsNull(ThisDocument.CustomDocumentProperties(“memoryPointer”)) Then
Set myRibbon = GetRibbon(ThisDocument.CustomDocumentProperties(“memoryPointer”))
Seems so far to work but I haven’t fully tested it. Strange that it would work that way and not just as a normal LongPtr. I figured there was some reason you didn’t just do that for excel and chose to store it in a cell or document property. Thanks so much for the guidance, and the code again.
Using VBA with Word is quite often challenging. Code that works with Excel often times needs tweaking to work with a Word document. And, the documentation is much more scarce than for Excel.
I also use Word’s Document Variables to store some of the same things that you might store in a custom property.