Hide individual custom ribbon buttons

Question

I have an custom excel ribbon and an excel add-in that contains a class which is instantiated once on opening a workbook. Based on some attributes of the class I need certain buttons from the custom ribbon (all in the same tab) to be hidden.

My custom ribbon is:

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

I then have the following VBA macros in a module to load the custom ribbon 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 workbook that the ribbon should load for I have a hidden sheet from which my class module class reads a value for each button to determine whether it should show or not. Once I've read this value how can I hide an individual button? All the examples I've found only seem to work for tabs. Could I pass the ribbonUI to the class and loop through each control? I haven't been able to find a method for doing this. Thanks for any help!

Answer 1

in Excel I have noticed that the ribbon that you embed in a WB will only appear when this workbook if visible (window opened not minimized). I have played with various visible and getvisible options but I was not able to get the ribbon to persist if the window of the workbook containing it is minimized. Workaround was to have a .xlam Addin containing the ribbon. Then ribbon is here no matter the state of the workbooks you open in Excel.

Answer 2

You need to customize the ribbon at run-time.

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

Instead of assigning a boolean true or false to the visible property of each button, you need another callback so that when this tab is loaded, the procedure checks whether your class object has been instantiated, and then sets true or false as necessary.

For example in my PPT part of my XML is like:

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

So instead of using the Visible property of the group, I use a custom attribute getVisible which calls a macro VisibleGroup. There are some nuances that I ran in to, like, I could not use the same callback/macro on different types of controls, hence why I have two callbacks (EnabledControl and VisibleGroup) both of which do exactly the same thing. I don't know why, and this part of development does not seem to be very well-documented, unfortunately.

Check my code to see all the places I put breakpoints while I was testing. I had to do quite a bit of debugging to get it to work. Put breakpoints in every procedure and step through your code. It is a pain in the ass, but if you've gotten this far, I'm sure you will be able to make it work.

UPDATE

I did a brief test on my PPT Add-In. Functionally this is similar so it is easier for me to test than trying to recreate everything in Excel.

My add-in has it's own Menu group, and some custom buttons. The relevant button line is this:

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

The 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 routine looks like this (I deliberately uncomment the MsgBox so that I can break & enter the code, you may want to do this just to debug and ensure that the proper Boolean val is being 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 modify the logic which assigns the returnedVal to suit your purposes. But basically this macro should fire every time that the button is about to be shown, so in my case it fires every time I open the Menu that contains it.

As long as the value of returnedVal is False before the Call RefreshRibbon(control.Id) then the procedure works and the button is no longer visible in my menu bar.