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