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
The Source
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
The Fix
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…