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

Feb 23, 2023 in Others by narikkadan
• 63,420 points
906 views

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, 2023 by Kithuzzz
• 38,010 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
• 63,420 points
410 views
0 votes
1 answer

Can an Excel xll function indicate that the return value should be displayed as a date and not a number?

To my knowledge, the only method to ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,420 points
513 views
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, 2023 in Others by narikkadan
• 63,420 points
2,121 views
0 votes
1 answer

Retrieve epay.info 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
911 views
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
3,233 views
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
• 23,260 points
524 views
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,670 points
765 views
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, 2023 in Others by Kithuzzz
• 38,010 points
218 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP