vba 隐藏单个自定义功能区按钮

Hide individual custom ribbon buttons

提问by baarkerlounger

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.

我有一个自定义 excel 功能区和一个 excel 加载项，其中包含一个在打开工作簿时实例化的类。根据类的某些属性，我需要隐藏自定义功能区（都在同一个选项卡中）中的某些按钮。

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:

然后我在一个模块中有以下 VBA 宏来加载自定义功能区和/或禁用它：

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!

在功能区应该为我加载的特定工作簿中，我有一个隐藏的工作表，我的类模块类从中读取每个按钮的值以确定它是否应该显示。阅读此值后，如何隐藏单个按钮？我发现的所有示例似乎只适用于选项卡。我可以将ribbonUI 传递给类并循环遍历每个控件吗？我一直找不到这样做的方法。谢谢你的帮助！

回答by David Zemens

You need to customize the ribbon at run-time.

您需要在运行时自定义功能区。

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

在这里检查我的问题（和答案），虽然我的问题是在 PPT VBA 中，但我在 Excel 中进行了测试，您的问题的解决方案应该非常相似。

Instead of assigning a boolean trueor falseto the visibleproperty 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 trueor falseas necessary.

您不需要为每个按钮的属性分配布尔值true或属性，而是需要另一个回调，以便在加载此选项卡时，该过程检查您的类对象是否已被实例化，然后根据需要设置或。falsevisibletruefalse

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

例如，在我的 XML 的 PPT 部分中，如下所示：

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

So instead of using the Visibleproperty of the group, I use a custom attribute getVisiblewhich calls a macro VisibleGroup. There are some nuances that I ran in to, like, I could not use the same callback/macro on different typesof controls, hence why I have two callbacks (EnabledControland 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.

因此Visible，我没有使用组的属性，而是使用getVisible调用宏的自定义属性VisibleGroup。我遇到了一些细微差别，例如，我无法在不同类型的控件上使用相同的回调/宏，因此为什么我有两个回调（EnabledControl和VisibleGroup），它们都做完全相同的事情。我不知道为什么，不幸的是，这部分开发似乎没有很好的文档记录。

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

我对我的 PPT 插件做了一个简短的测试。从功能上讲，这是相似的，因此对我来说，测试比尝试在 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：

<?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 EnableControlroutine looks like this (I deliberately uncomment the MsgBoxso 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):

该EnableControl程序看起来像这样（我故意取消注释MsgBox，这样我可以打破与输入代码，您可能希望这样做只是为了调试，并确保适当的布尔VAL被传递到控制）：

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 returnedValto suit your purposes. But basically this macro should fire every timethat the button is about to be shown, so in my case it fires every time I open the Menu that contains it.

您将需要修改分配 的逻辑returnedVal以满足您的目的。但基本上这个宏应该在每次显示按钮时触发，所以在我的情况下，每次我打开包含它的菜单时它都会触发。

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

只要值returnedVal是False之前Call RefreshRibbon(control.Id)那么程序的工作原理和按钮不再可见在我的菜单栏。

回答by bernso

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.

在 Excel 中，我注意到您嵌入到 WB 中的功能区仅在此工作簿可见时才会出现（打开的窗口未最小化）。我玩过各种可见和可获取的选项，但如果包含它的工作簿的窗口被最小化，我无法让功能区保持不变。解决方法是使用包含功能区的 .xlam 插件。无论您在 Excel 中打开的工作簿的状态如何，功能区都在这里。