My VBA Addin creates a custom ribbon tab but should only be visible for intended workbooks

Question

In order to access the macros and functions I require in specific workbooks, which I can identify using various criteria, I have developed an add-in for Excel. Access is made simple with a ribbon tab. The ribbon tab shouldn't be shown when I (or other users) open a brand-new workbook or a workbook that doesn't meet the requirements, but it should be available when I move back to the right workbook. If I understand correctly, if the ribbon tab is in an xlsm, this happens automatically, but I need it in an external XLAM.

I had hoped that the getVisible="MyGetVisible" on the tab line would be able to serve as an on/off switch when it was set to True (for the wb where the ribbon tab should show) and False (for the others) and the ribbon was invalidated. However, the XLAM has XML code that looks like the below (some more, but this is the essential).

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
    onLoad="CallbackOnLoad">
    <ribbon>
        <tabs>
            <tab id="MyTab" label="My Custom tab" insertBeforeMso="TabHome" getVisible="MyGetVisible">
                        <group idMso="GroupClipboard" />
                <group id="MyGroup1" label="Sheet Navigation">
        <dropDown id="MyDD1" label="Data Sheets" showLabel="false" imageMso="TextAlignGallery" getVisible="MyGetVisible2" getItemCount="MyGetItemCount" getItemID="MyGetItemID" sizeString="xxxxxxxxxx" getItemLabel="MyGetItemLabel" getSelectedItemID="MyGetSelectedItemID" onAction="MyOnAction"/>
            </group>            

                </group>                        
            </tab>
        </tabs>
    </ribbon>
</customUI>

And the VBA for MyGetVisible is

Public Sub GetVisible(control As IRibbonControl, ByRef visible)
    visible = (ActiveWorkbook.name = "MyWBwhereTheRibbonTabShouldShow.xlsm")
End Sub

Several of my tests using DoEvents and Wait are displayed in my RibRefresh. The issue is unrelated to the Ribbon ID being stored (although it seemed like it is safer to store the ID in a named cell in the xlam than to store it in a created name not referring to a range)

Public Sub RibRefresh()
Debug.Print ActiveWorkbook.name
    If MyRibbon Is Nothing Then RibRetrieve
    DoEvents
    Application.Wait Now + 0.00001
    MyRibbon.Invalidate
    DoEvents
    If err.Number > 0 Then
        MsgBox "The Ribbon-pointer is lost, save-close & restart workbook instead"
        err.Clear
    End If
End Sub

It seems like it should work, but it doesn't. Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526) 32-bit

Answer 1

The RibRefresh sub's logic is overly convoluted using Wait, DoEvents, and other constructs. The onLoad callback, where it is supplied as a parameter, is the only place where it is possible to access the ribbon object instance (more precisely, an instance of the IRibbonUI interface). For later use, you must save/store it in the local object. Here is an illustration of how the declaration appears in the ribbon XML:

<customUI … OnLoad="MyAddinInitialize" …>

and the callback:

Dim MyRibbon As IRibbonUI 
 
Sub MyAddInInitialize(Ribbon As IRibbonUI) 
 Set MyRibbon = Ribbon 
End Sub 

Then where required you may call the Invalidate method to get your callbacks invoked anew:

Sub myFunction() 
 MyRibbon.Invalidate() ' Invalidates the caches of all of this add-in's controls 
End Sub