Hide individual custom ribbon buttons

Question

I have a special excel feed and an excel add-in that contains a class that is created once when the book is opened. Based on some class attributes, I need hidden buttons from the user feed (all on the same tab).

My custom feed:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="loadCustom"> <ribbon> <tabs> <tab id="tab1" label="customTab" getVisible="GetVisible" tag="myTab"> <group id="grp1" label="Group1" imageMso="ViewFullScreenView" getVisible="GetVisible"> <button id="Bt1" size="large" label="Button1" imageMso="AccessListIssues" onAction="runBt1" visible="true"/> <button id="Bt2" size="large" label="Button2" imageMso="AccessListTasks" onAction="runBt2" visible="true"/> <button id="Bt3" size="large" label="Button3" imageMso="ControlLayoutStacked" onAction="runBt3" visible="true"/> <button id="Bt4" size="large" label="Button4" imageMso="ControlLayoutTabular" onAction="runBt4" visible="true"/> </group> </tab> </tabs> </ribbon> </customUI> 

Then I have the following VBA macros in the module to load the user feed and / or disable it:

 Public Sub loadCustom(ribbon As IRibbonUI) Set RibUI = ribbon If workbookTitle = "myWorkbook" Then MyTag = "show" Else MyTag = False RefreshRibbon MyTag End If End Sub Sub GetVisible(control As IRibbonControl, ByRef visible) If MyTag = "show" Then visible = True Else If control.Tag Like MyTag Then visible = True Else visible = False End If End If End Sub Sub RefreshRibbon(Tag As String) MyTag = Tag If RibUI Is Nothing Then MsgBox "Error, Save/Restart your workbook" Else RibUI.Invalidate End If End Sub 

In my specific book that the feed should load, I have a hidden sheet from which the class class of the class reads the value for each button to determine whether it should be displayed or not. As soon as I read this value, how to hide a separate button? All the examples I found seem to work only for tabs. Can I pass ribbonUI to a class and skip each control? I could not find a way to do this. Thanks for any help!

Answer 1

You need to configure the tape at runtime.

Check my question (and answer) here , although my problem was in PPT VBA, I tested in Excel, and the solution to your problem should be very similar.

Instead of setting Boolean true or false for the visible property for each button, you need a different callback so that when loading this tab, the procedure checks whether the class object was created, and then sets true or false as necessary.

For example, in my PPT part of my XML:

 ... <tab idMso="TabView"> <group idMso="GroupMasterViews" getVisible="VisibleGroup"/> <group idMso="GroupPresentationViews" getVisible="VisibleGroup"/> </tab> ... 

Therefore, instead of using the visible property for the group, I use the getVisible custom attribute, which invokes the VisibleGroup macro. There are some nuances that I encountered, for example, I could not use the same callback / macro for different types of controls, so I have two callbacks ( EnabledControl and VisibleGroup ), both of which do exactly the same . I do not know why, and this part of the development does not seem to be very well documented, unfortunately.

Check my code to see all the places where I set breakpoints during testing. I had to debug a bit to make it work. Put breakpoints in each procedure and execute your code. It’s a pain in the ass, but if you get to that, I’m sure you can make it work.

UPDATE

I did a quick test on my PPT add-in. This is functionally similar, so it's easier for me to test than trying to recreate everything in Excel.

My add-in has its own menu group and some custom buttons. Corresponding line of buttons:

 <button id="HelpButton" label="Help" getVisible="EnableControl" onAction="HelpFile" /> 

Full XML for your reference:

 <?xml version="1.0" encoding="UTF-8" standalone="yes"?> <customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <commands> <command idMso="ViewSlideSorterView" getEnabled="EnableControl"/> <command idMso="ViewNotesPageView" getEnabled="EnableControl"/> <command idMso="ViewSlideShowReadingView" getEnabled="EnableControl"/> <command idMso="ViewSlideMasterView" getEnabled="EnableControl"/> <command idMso="ViewHandoutMasterView" getEnabled="EnableControl"/> <command idMso="ViewNotesMasterView" getEnabled="EnableControl"/> <command idMso="WindowNew" getEnabled="EnableControl"/> </commands> <ribbon startFromScratch="false"> <tabs> <tab idMso="TabView"> <group idMso="GroupMasterViews" getVisible="VisibleGroup"/> <group idMso="GroupPresentationViews" getVisible="VisibleGroup"/> </tab> <tab id="TabTiger" label="Chart Builder" insertAfterMso="TabDeveloper"> <group id="GroupTigerMain" label="XXXX Chart Builder"> <menu id="TigerMenu" image="XXXXLogo" size="large"> <button id="LaunchButton" label="Launch Chart Builder" onAction="ShowChart_Form" /> <button id="InfoButton" label="Info" onAction="Credit_Inf" /> <button id="VersionButton" label="Version" onAction="VersionNum" /> <button id="HelpButton" label="Help" getVisible="EnableControl" onAction="HelpFile" /> </menu> </group> </tab> </tabs> </ribbon> </customUI> 

The EnableControl procedure looks like this (I will intentionally uncomment the MsgBox so that I can break and enter the code, you can do this just for debugging and ensuring that the correct logical val is passed to the control):

 Sub EnableControl(control As IRibbonControl, ByRef returnedVal) returnedVal = Not TrapFlag 'TrapFlag = True indicates the Application is running. MsgBox ("GetEnabled for " & control.Id) 'Debug.Print control.Id & " enabled = " & CStr(returnedVal) Call RefreshRibbon(control.Id) End Sub 

You will need to change the logic that assigns returnedVal to your needs. But basically, this macro should fire every time the button is displayed, so in my case it fires every time I open the menu containing it.

As long as the returnedVal value is false until Call RefreshRibbon(control.Id) , then the procedure works and the button no longer appears in my menu bar.

Answer 2

in Excel. I noticed that the tape that you insert into WB will only be displayed if this book is visible (an open window is not minimized). I played with various visible and accessible options, but I could not get the tape to save if the workbook window containing it was minimized. The workaround was to have a .xlam Addin containing the tape. Then the tape here does not depend on the state of the books that you open in Excel.