Excel VBA hiding custom ribbon tab

Question

I have a custom ribbon which works fine but I only want to enable it and have my add in show for certain workbooks so I check for the workbook title on load and try to use the Invalidate method if the condition is false. Unfortunately nothing happens the custom ribbon tab is still showing.

The following is my sub:

Public Sub loadMyRibbon(ribbon As IRibbonUI)

    Set RibUI = ribbon

    If Not workbookTitle = "My Workbook" Then
        If Not RibUI Is Nothing
            RibUI.Invalidate
            MsgBox "Not Working"
        End If
    End If

End Sub

Which seems correct to me from reading through the method documentation: Microsoft Documentation

I see my MsgBox message displayed on the screen so I know the code is executing correctly up to that point but RibUI.Invalidate doesn't hide my tab. Appreciate any pointers!

I have also tried:

RibUI.InvalidateControl "myTag"

But this also doesn't work

Vikas Vikas · Accepted Answer · 2013-08-02T10:23:16

Ribbon.Invalidate doesn't mean the ribbon will not show. Invalidate function just tells the ribbon to invalidate and re-initialize the ribbon controls with their default/dynamic properties.

I worked with few Add-ins where the clients wanted to hide the ribbon items if users cannot pass the authentication. So in such a case, I used "GetVisible" attribute in all of my Control and then I used this code

Sub GetVisible(control As IRibbonControl, ByRef Visible)
    On Error Resume Next

    Visible = shouldShowOrNot
End Sub

shouldShowOrNot is a boolean variable, which I set in Ribbon Load to true if user passes the authentication. See the following image:

Ribbon with Failed Authentication

Now the above image is a representation of Ribbon in case User has failed the authentication. There might be a better way to do it, but i found it to be the best way so far.

Hope this helps, Vikas B

Excel VBA hiding custom ribbon tab

1 Answers