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

0 votes

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=""
            <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"/>


And the VBA for MyGetVisible is

Public Sub GetVisible(control As IRibbonControl, ByRef visible)
    visible = ( = "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()
    If MyRibbon Is Nothing Then RibRetrieve
    Application.Wait Now + 0.00001
    If err.Number > 0 Then
        MsgBox "The Ribbon-pointer is lost, save-close & restart workbook instead"
    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

Feb 23 in Others by narikkadan
• 60,820 points

1 answer to this question.

0 votes

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
answered Feb 23 by Kithuzzz
• 35,300 points

Related Questions In Others

0 votes
1 answer

How should I show the Developer tab in Excel using Macros? (Excel VBA)

You can activate (mode=1) or deactivate (mode=0) ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 60,820 points
0 votes
1 answer
0 votes
1 answer

How to increment the Range of a For Each loop - Excel VBA

Your formula seems to sum 1 single ...READ MORE

answered Jan 7 in Others by narikkadan
• 60,820 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,970 points
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
0 votes
1 answer

Cross sum from a number but cross sum should total to one digit

Try this: Option Explicit Sub Quersumme_OneDigit() ...READ MORE

answered Mar 17 in Others by Kithuzzz
• 35,300 points
0 votes
1 answer
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP