Handling Office custom IRibbonUI Lost State Errors

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 error (mostly intermittently, and unexpectedly) there may be a fix. I have found a solution for my VBA code.

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 comes from Ron de Bruin. I used Ron’s workbook code as it was easy to follow and commented well. What I have in this post is my application of their work. I simplified the code and added some error handling to suit my needs. Read both of these other posts and see if you are a candidate for this remedy. Hopefully, it works for you, too.

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 follow

' 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…

2 thoughts on “Handling Office custom IRibbonUI Lost State Errors

  1. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.