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!

score 7 · Accepted Answer · edited May 23 '17 at 11:53

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.

@db579 did you also add a `getVisible` subroutine in the workbook's VBProject? (kinda silly question, but just want to make sure you didn't overlook the obvious) Also, I notice you use the same `getVisible` custom attribute on a Group control. In my application, **I could not use the same callback/macro on different types of controls** -- if you're using `getVisible` on a Group control ("group1"), try using a different callback (even if it's duplicate code) on the buttons. Let me know, I can try to test it out with a button later this evening. — David Zemens, Aug 04 '13 at 18:29
Yes I did add the corresponding macro to the project and I removed the callback from the group so there shouldn't be any conflict there. I do use the get visible callback for the tab but I named the macro something different (I assumed the getVisible='' bit had to be called that to work? — baarkerlounger, Aug 04 '13 at 23:59
See my revision above, I was able to disable/enable a button using the `getVisible` attribute in the XML. I would try using a different macro, so e.g., you can use `getVisible="EnabledControl"` and then try adding in the `EnabledControl` subroutine. — David Zemens, Aug 05 '13 at 01:45
Also, note that when you're using the CustomUI ribbon editor, it's usually necessary to save and close the XML, before opening the Excel file, and likewise you have to close the Excel file before re-opening the XML in the CustomUI, or it reverts/doesn't save changes when the file is open in both applications concurrently. — David Zemens, Aug 05 '13 at 01:46
Hmm I'm at a bit of a loss here I've added the `getVisible="EnabledControl"` and corresponding macro exactly as you have and it makes my custon ribbon not load at all. Remove the `getVisible` and it loads as before... I'm only using it on one button so no naming conflicts and I don't use the CustomUI ribbon editor I just edit the xml in notepad and add to the zip folder — baarkerlounger, Aug 05 '13 at 01:58
Ah I understand now - I hadn't removed the visible attribute from the xml. Thank you very much for your help it's much appreciated! — baarkerlounger, Aug 05 '13 at 02:39

score 0 · Answer 2 · answered Jul 10 '14 at 07:50

0

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.

answered Jul 10 '14 at 07:50

bernso

1

1

This is by design: the CustomUI Ribbon is local to the file which contains the Ribbon XML markup; this way, your custom ribbon works only on the specific file. As you note, if you save the RibbonUI in an XLAM file, it will be visible to all open Workbooks (or, you can add conditional logic to only display it in workbooks meeting some user-defined criteria, etc.). – David Zemens Aug 03 '16 at 14:22

Hide individual custom ribbon buttons

2 Answers2

Linked