vba Excel 隐藏/显示功能区上除自定义选项卡之外的所有选项卡

Excel Hide/Show all tabs on Ribbon except custom tab

提问by Brett

How can I hide and show all the standard Excel ribbon tabs using VBA (not XML). I do not want to hide the whole ribbon (as is asked here: VBA minimize ribbon in Excel) just the tabs. I know how to use startFromScratch using XML so please do not suggest that or other XML solutions.

如何使用 VBA（而非 XML）隐藏和显示所有标准 Excel 功能区选项卡。我不想隐藏整个功能区（如此处所问：VBA 最小化 Excel 中的功能区）只是选项卡。我知道如何使用 XML 使用 startFromScratch，所以请不要建议使用它或其他 XML 解决方案。

So far I have done an extensive Google search and looked at:

到目前为止，我已经进行了广泛的谷歌搜索并查看了：

• http://msdn.microsoft.com/en-us/library/office/ee390805(v=office.11).aspx
• http://msdn.microsoft.com/en-us/library/microsoft.office.tools.ribbon.officeribbon.startfromscratch.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
• Customizing a ribbon with VBA in Excel
• Show Excel 2007 Ribbon in XLS file using Excel VBA
• Show Excel 2007 Ribbon in XLS file using Excel VBA
• Ribbon GUI Guidelines
• Excel CustomUI ribbon layout
• http://www.rondebruin.nl/win/s2/win012.htm

• http://msdn.microsoft.com/en-us/library/office/ee390805(v=office.11​​).aspx
• http://msdn.microsoft.com/en-us/library/microsoft.office.tools.ribbon.officeribbon.startfromscratch.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1
• 在 Excel 中使用 VBA 自定义功能区
• 使用 Excel VBA 在 XLS 文件中显示 Excel 2007 功能区
• 使用 Excel VBA 在 XLS 文件中显示 Excel 2007 功能区
• 功能区 GUI 指南
• Excel CustomUI 功能区布局
• http://www.rondebruin.nl/win/s2/win012.htm

What I am saying is I have already done an extensive search and tried many things without getting a result.

我要说的是我已经进行了广泛的搜索并尝试了很多事情但没有得到结果。

回答by Siddharth Rout

How can I hide and show all the standard Excel ribbon tabs using VBA (not XML)

如何使用 VBA（而非 XML）隐藏和显示所有标准 Excel 功能区选项卡

The answer is "YOU CAN'T".

答案是“你不能”。

AFAIK, you can't do that using VBA. Unfortunately VBA doesn't expose the tabs. The only options that you have are as shown in the image below

AFAIK，您不能使用 VBA 做到这一点。不幸的是，VBA 没有公开选项卡。您拥有的唯一选项如下图所示

So you can work with the commandbar, commandbarButton, commandbarComboBox etc...

所以你可以使用 commandbar、commandbarButton、commandbarComboBox 等...

You can say that Set cbar = Application.CommandBars("Ribbon")but after that, the problem that you will face is how to get a handle for the tabs.

您可以这么说，Set cbar = Application.CommandBars("Ribbon")但在那之后，您将面临的问题是如何获得选项卡的句柄。

What you can do with the Ribbon using VBA:

您可以使用 VBA 使用功能区做什么：

• Determine whether a particular control is Enabled/Visible/Pressed(Toggleboxes/CheckBoxes)
• Get a control's label, screen tip, or supertip Display the image associated with a control.
• Execute a particular control.

• 确定特定控件是否启用/可见/按下（Toggleboxes/CheckBoxes）
• 获取控件的标签、屏幕提示或超级提示 显示与控件关联的图像。
• 执行特定的控制。

What you can't do with the Ribbon using VBA:

使用 VBA 不能用功能区做什么：

• Determine which tab is currently selected.
• Activate a particular tab.
• Hide a particular tab
• Add a new tab.
• Add a new group to a tab.
• Add a new control.
• Remove/Disable/Hide a control.

• 确定当前选择了哪个选项卡。
• 激活特定选项卡。
• 隐藏特定选项卡
• 添加一个新选项卡。
• 将新组添加到选项卡。
• 添加新控件。
• 删除/禁用/隐藏控件。

You can however use XML to achieve what you want. For example

但是，您可以使用 XML 来实现您想要的。例如

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabReview" visible="false" />
        </tabs>
    </ribbon>
</customUI>

But I guess you do not want to go via the XML Route.

但我猜你不想通过 XML 路由。

回答by user6620568

You can indeed hide/show ribbons using VBA. Here is an example:

您确实可以使用 VBA 隐藏/显示色带。下面是一个例子：

<ribbon startFromScratch="false">
  <tabs>
  <!-- EXCEL BUILT-IN TABS -->
    <tab idMso="TabDeveloper" getVisible="GetVisible">
   <group idMso="GroupCode" visible="true"/>
   <group idMso="GroupAddins" visible="true"/>
   <group idMso="GroupControls" visible="true"/>
   <group idMso="GroupXml" visible="true"/>
   <group idMso="GroupModify" visible="true"/>
 </tab>
  </tabs>
</ribbon>

1. Setup your XML file.

2. Setup your VBA script. Sub GetVisible(control As IRibbonControl, ByRef MakeVisible) Select Case control.ID Case "TabDeveloper": MakeVisible = True Case "TabHome": MakeVisible = True Case "TabInsert": MakeVisible = True Case "TabPageLayoutExcel": MakeVisible = True Case "TabFormulas": MakeVisible = True Case "TabData": MakeVisible = True Case "TabReview": MakeVisible = True Case "TabView": MakeVisible = True Case "TabAddIns": MakeVisible = True Case "TabBackgroundRemoval": MakeVisible = True End Sub

3. Download this file for a list of Control IDs for MS Office. http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=6627

1. 设置您的 XML 文件。

2. 设置您的 VBA 脚本。 Sub GetVisible(control As IRibbonControl, ByRef MakeVisible) Select Case control.ID Case "TabDeveloper": MakeVisible = True Case "TabHome": MakeVisible = True Case "TabInsert": MakeVisible = True Case "TabPageLayoutExcel": MakeVisible = True Case "TabFormulas": MakeVisible = True Case "TabData": MakeVisible = True Case "TabReview": MakeVisible = True Case "TabView": MakeVisible = True Case "TabAddIns": MakeVisible = True Case "TabBackgroundRemoval": MakeVisible = True End Sub

3. 下载此文件以获取 MS Office 的控件 ID 列表。 http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=6627

Tip: Be sure to close your Excel Workbook that includes the ribbon you are editing before editing the XML ribbon file. I have found that it sometimes erases my VBA code (I don't know why, it just does).

提示: 在编辑 XML 功能区文件之前，请务必关闭包含您正在编辑的功能区的 Excel 工作簿。我发现它有时会擦除我的 VBA 代码（我不知道为什么，它确实如此）。

回答by sysmod

To Activate a particular tab

激活特定选项卡

In Microsoft Office 2010, you activate a custom tab by using the ActivateTab method of the IRibbonUI object

You use the ActivateTabMso method for built-in tabs and ActivateTabQ for tabs shared between multiple add-ins.

ActivateTabQ also requires an additional String-type parameter that specifies the namespace of the add-in

在 Microsoft Office 2010 中，您可以使用 IRibbonUI 对象的 ActivateTab 方法激活自定义选项卡

对内置选项卡使用 ActivateTabMso 方法，对在多个加载项之间共享的选项卡使用 ActivateTabQ。

ActivateTabQ 还需要一个额外的字符串类型参数来指定加载项的命名空间

In XML specify

在 XML 中指定

customUI onLoad="RibbonOnLoad" xmlns=etc

In VBA

在 VBA 中

Public gRibbonUI As IRibbonUI 

Sub RibbonOnLoad(ribbon As IRibbonUI)

   Set gRibbonUI = ribbon

End Sub

Then in code

然后在代码中

gRibbonUI.ActivateTab "MyTabID"

gRibbonUI.ActivateTabMso "TabHome"

回答by Seshadri

First, open the Excel sheet which you want hide the ribbon tab on, then press Alt+F11. Insert new code module and add following code:

首先，打开要隐藏功能区选项卡的 Excel 工作表，然后按Alt+ F11。插入新的代码模块并添加以下代码：

Private Sub hide()
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)
End sub

回答by Evert

You can use on the XML like these:

您可以像这样在 XML 上使用：

< ribbon startFromScratch="true" >

<ribbon startFromScratch="true">

check this link:

检查此链接：

http://www.rondebruin.nl/win/s2/win012.htm

http://www.rondebruin.nl/win/s2/win012.htm

回答by PatricK

Try this XML for Excel I have tested:

试试这个 XML for Excel 我已经测试过：

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
    <tab idMso="TabHome" visible="false" />
    <tab idMso="TabInsert" visible="false" />
    <tab idMso="TabFormulas" visible="false" />
    <tab idMso="TabData" visible="false" />
    <tab idMso="TabReview" visible="false" />
    <tab idMso="TabView" visible="false" />
    <tab idMso="TabDeveloper" visible="false" />
</tabs>
</ribbon>
</customUI>

Result:

结果：