Excel VBA hiding custom ribbon tab

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


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:

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

Need Your Help

Excel VBA Copying to Blank Row

excel vba excel-vba

Am having some difficulty with a VBA subroutine that takes information entered on the first sheet of an Excel workbook - "Entry", and pastes it into a separate sheet inside the same workbook -

SERP is just showing the main page but none of the subpages

javascript html ajax seo google-search

Have a look on this search where you can see that just my main page is indexed.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.