CODEWITHSUNDEEP
excelvbaexcel-addinsribbonx
JoMo
JoMo

Reputation: 1

Addin custom ribbon tab but visible only for intended workbooks

I created an addin for Excel I need to access in certain workbooks, which I can identify by some criteria.

There is a ribbon tab that makes access easy. When I (or other users) open a new workbook or a workbook that doesn't fulfill the criteria, the ribbon tab should not be visible, but still be visible when I switch back to the correct workbook.

If I understand right, this is automatic if the ribbon tab is in an xlsm, but I need to have it in an external XLAM.

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

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

VBA code for MyGetVisible

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

My RibRefresh shows some of my tests, with DoEvents and Wait. The Ribbon ID is stored and that is not the problem. (Although it seemed 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

Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526) 32-bit

Upvotes: 0

Views: 527

Answers (2)

JoMo
JoMo

Reputation: 1

I finally managed to get it to work as I wanted, and what I did was to make sure the GetVisible macro for the tab wasn't called "GetVisible" but a different name like I suggested above but didn't do: "MyGetVisible", I don't know if it gets confused or it was multiple things happening.

I also changed the code in ThisWorkbook of the one where I wanted the xlam's tab to be visible, since the execution sequence between the tab's callback and the Workbook_activate wasn't what I expected. So I added a test to make sure it didn't execute before MyXLAM had a value (which it gets in the tab's Callback)

Option Explicit
Dim bHasOpened As Boolean
Private Sub Workbook_Activate()
    If bHasOpened Then Application.Run MyXLAM & "RibRefresh"
End Sub
Private Sub Workbook_Deactivate()
    bHasOpened = True
    Application.Run MyXLAM & "RibRefresh"
End Sub

Thanks for the help!

Upvotes: 0

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

The logic in the RibRefresh sub is overcomplicated with DoEvents and Wait etc. The only possible place where you can retrieve the ribbon object instance (to be precise, an instance of the IRibbonUI interface) is the onLoad callback where it is passed as a parameter. You need to save/store it in the local object for further usage. For example, here is how the declaration looks 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

Note, if an add-in writer implements the getVisible callback procedure for a control/tab, the function is called once, the state loads, and then if the state needs to be updated, the cached state is used instead of recalling the procedure. This process remains in place until the add-in signals that the cached values are invalid by using the Invalidate method, at which time, the callback procedure is again called and the return response is cached.

It seems you need to handle scenarious where the workbook is opened, activated, closed and etc. to keep the ribbon UI up to date by calling the Invalidate method of the IRibbonUI interface.

Upvotes: 2

Related Questions