Invalidate Ribbon Control Without Module-Level Variables

Question

I've developed an Excel add-in that includes a custom ribbon. I'd like to be able to invalidate (enable/disable) a control on the ribbon in certain situations, but every example I can find uses a module-level or global variable to store the ribbon object when the ribbon is first loaded. This seems like a good way to do it, but, as listed here, there are instances when variables can empty.

So I'm wondering, is there a different way to achieve the result of enabling/disabling a control in an Excel ribbon without using a variable to store the ribbon object or without even using the invalidate method at all?

score 5 · Accepted Answer · answered Jan 10 '16 at 17:30

After reading your description I assume that you has developed a pure Excel VBA Add-In (instead of e.g. an Excel VSTO Add-In). Therefore, I'm afraid that there is no other way to achieve your goal. Fortunately, there is a workaround to restore the object reference to the ribbon object after a reset.

Workaround: Within the "Ribbon_Load" event handler, where you would set the object reference to the Excel ribbon object, you should also save the "ObjPtr()" value of the ribbon object (e.g. within a worksheet cell). For example like this:

Public gobjRibbon As Office.IRibbonUI

' Callback for customUI.onLoad
Sub Ribbon_Load(ribbon As Office.IRibbonUI)

    Set gobjRibbon = ribbon

    SampleWorksheet.Cells(1,1).Value = ObjPtr(ribbon)
End Sub

In doing so, you can later restore the reference to the ribbon object (if necessary). You can achieve this by calling the "RefreshRibbon" procedure (which also invalidates the whole ribbon) from the following example:

#If VBA7 Then
    Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
        ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)
#Else
    Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
        ByRef Destination As Any, ByRef Source As Any, ByVal Length As Long)
#End If

#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

    Call CopyMemory(objRibbon, lRibbonPointer, LenB(lRibbonPointer))

    Set GetRibbon = objRibbon
    Set objRibbon = Nothing
End Function

Public Sub RefreshRibbon()

    If gobjRibbon Is Nothing Then
        Set gobjRibbon = GetRibbon(SampleWorksheet.Cells(1,1).Value)
    ' Else: Do nothing!
    End If

    On Error Resume Next
    gobjRibbon.Invalidate
    On Error GoTo 0
End Sub

I recommend to clear the auxiliary cell at the end of an Excel session, because otherwise Excel suprisingly crashes sometimes.

Alternative: Re-develop your VBA Add-In as a VSTO Add-In to avoid having trouble with lost object references.

Thanks, Patrick. Instead of storing the pointer value in a cell, I used a ***hidden name***, which I delete when the workbook closes. I figure this will minimize the possibility of it being accidentally altered. Also, immediately before the call to `Invalidate`, I check that `gobjRibbon` is not `Nothing`. I do this because I change the `enabled` property of some of the buttons on my ribbon based on which sheet is active; and `WorksheetActivate` is called ***before*** the ribbon is loaded, and the object pointer has been set. This has brought my crashes to zero. Thanks again. — Rocky Scott, Aug 02 '18 at 15:14
Hi Rocky, I like your idea using a **hidden name** instead of a **cell**. Thanks for sharing and for your feedback! — Patrick Brunck, Jan 23 '19 at 13:17

Invalidate Ribbon Control Without Module-Level Variables

1 Answers1

Linked