vba Excel 2013中的VBA自动隐藏功能区

VBA auto hide ribbon in Excel 2013

提问by Przemyslaw Remin

How to Auto-hide Ribbonin Excel 2013 in VBA? I would like to achieve exactly what I get by clicking on the upper arrow icon at the right top of Excel menu marked with blue in the picture below and then clicking on the first option marked with orange:

如何Auto-hide Ribbon在 Excel 2013 中使用 VBA？我想通过单击下图中标有蓝色的 Excel 菜单右上角的上箭头图标，然后单击标有橙色的第一个选项来准确实现我所获得的效果：

I would be also interested in VBA switching back to the third option Show Tabs and Commands. Important thing for me is to keep in the Excel menu the upper arrow icon (marked with blue).

我也会对 VBA 切换回第三个选项感兴趣Show Tabs and Commands。对我来说重要的是在 Excel 菜单中保留上箭头图标（标有蓝色）。

I have tried hints shown in this thread: VBA minimize ribbon in Excelbut I am not satisfied with results.

我尝试过此线程中显示的提示：VBA 最小化 Excel 中的功能区，但我对结果不满意。

Attempt 1

尝试 1

Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)

This is good but hides the blue icon.

这很好，但隐藏了蓝色图标。

Attempt 2

尝试 2

CommandBars.ExecuteMso "MinimizeRibbon"

This is close to what I want. This keeps the blue icon but does not hide the entire menu. It switches to the second option displayed in the picture Show Tabs.

这接近我想要的。这会保留蓝色图标，但不会隐藏整个菜单。它切换到图片中显示的第二个选项Show Tabs。

Attempt 3

尝试 3

SendKeys "^{F1}"

The attampt does not work at all. Moreover, it is supposed to imitate the attempt 2. So even that would not satisfy me.

attamt 根本不起作用。而且，它应该模仿尝试2。所以即使这样我也不会满意。

回答by Tyeler

I can't see that anyone else has brought this up... This isn't a workaround, this is the actual idMSO for what I think you're looking for. This code makes my excel window look like everything is gone the same way the first option does for Auto-Hide Ribbon.

我看不到其他人提出了这个问题...这不是解决方法，这是我认为您正在寻找的实际 idMSO。这段代码使我的 excel 窗口看起来像第一个选项一样，一切都消失了Auto-Hide Ribbon。

Before the code runs, my window looks like this, in the 'Restore' size:

在代码运行之前，我的窗口看起来像这样，“恢复”大小：

Running the following code:

运行以下代码：

Sub HideTheRibbon()
    CommandBars.ExecuteMso "HideRibbon"
End Sub

Will make your window look like this, in the maxamized window size (just like what would happen if you were to press the Auto-Hide Ribbonbutton manually):

将使您的窗口看起来像这样，在最大化的窗口大小中（就像如果您Auto-Hide Ribbon手动按下按钮会发生的情况）：

If you want the ribbon automatically hidden when the workbook opens, put this in the workbook code:

如果您希望在工作簿打开时自动隐藏功能区，请将其放入工作簿代码中：

Sub Workbook_Open()
    CommandBars.ExecuteMso "HideRibbon"
End Sub

Alternatively, to achieve the same thing, you could put this code in a module:

或者，为了实现相同的目的，您可以将此代码放在一个模块中：

Sub Auto_Open()
    CommandBars.ExecuteMso "HideRibbon"
End Sub

If you want the window to revert back to normal, you run the exact same code again.In other words, the following code would make no visual change at all when ran because the idMSO "HideRibbon" is a toggleButton:

如果您希望窗口恢复正常，请再次运行完全相同的代码。换句话说，以下代码在运行时根本不会有任何视觉变化，因为 idMSO“HideRibbon”是一个切换按钮：

Sub HideTheRibbon()
    CommandBars.ExecuteMso "HideRibbon"
    CommandBars.ExecuteMso "HideRibbon"
End Sub

If you want a full list of all the idMSO in excel, click the following that apply to you: Excel 2013+, Excel 2010, Excel 2007

如果您需要 Excel 中所有 idMSO 的完整列表，请单击以下适用于您的列表：Excel 2013+、Excel 2010、Excel 2007

回答by Olandu17

I use this for presentation purposes

我将其用于演示目的

ActiveWindow.DisplayGridlines = False

ActiveWindow.DisplayGridlines = False

ActiveWindow.DisplayHeadings = False

ActiveWindow.DisplayHeadings = False

Application.DisplayFormulaBar = False

Application.DisplayFormulaBar = False

Application.DisplayFullScreen = TrueThis is what i used to hide the ribbon

Application.DisplayFullScreen = True这是我用来隐藏功能区的

回答by Vityata

Probably you should do something a little more complicated:

可能你应该做一些更复杂的事情：

Use CommandBars.ExecuteMso "MinimizeRibbon"to show/hide the ribbon. Depending on what you want, you may show/hide all other tabs in the ribbon. E.g. use something of the code here -> Excel Hide/Show all tabs on Ribbon except custom tab

使用CommandBars.ExecuteMso "MinimizeRibbon"显示/隐藏功能区。根据您的需要，您可以显示/隐藏功能区中的所有其他选项卡。例如，在此处使用某些代码 -> Excel 隐藏/显示功能区上除自定义选项卡之外的所有选项卡

Thus 2 steps:

因此2个步骤：

Step 1 - show or hide with the CommandBars.ExecuteMso

第 1 步 - 使用 CommandBars.ExecuteMso 显示或隐藏

Step 2 - show or hide the rest of the tabs with some macros from the link.

第 2 步 - 使用链接中的一些宏显示或隐藏其余选项卡。

A little big workaround, but you will get what you want.

有点大的解决方法，但你会得到你想要的。

回答by Miller

I call this macro on Workbook_Open to check for the ribbon and if not hidden, it will hide the ribbon (I actually have it located in another Sub that also removes the formula bar, status bar, headings, and gridlines at Workbook_Open)...

我在 Workbook_Open 上调用这个宏来检查功能区，如果没有隐藏，它将隐藏功能区（我实际上将它放在另一个 Sub 中，该 Sub 也删除了 Workbook_Open 的公式栏、状态栏、标题和网格线）...

Sub HideRibbon()
If CommandBars("Ribbon").Controls(1).Height < 100 Then
Exit Sub
Else
    CommandBars.ExecuteMso ("MinimizeRibbon")
End If
End Sub

Then I call this macro on Workbook_BeforeClose to check for the ribbon and if it is not shown, it will show the ribbon for the next excel spreadsheet that is opened.

然后我在 Workbook_BeforeClose 上调用这个宏来检查功能区，如果它没有显示，它将显示下一个打开的 Excel 电子表格的功能区。

Sub ShowRibbon()
If CommandBars("Ribbon").Controls(1).Height > 100 Then
Exit Sub
Else
    CommandBars.ExecuteMso ("MinimizeRibbon")
End If
End Sub

This eliminates the chance of hiding the ribbon when the workbook is opened and a user then manually showing the ribbon which in turn would reverse the show on close and actually hide the ribbon. On open, the ribbon would then be shown again. This will keep it the same every time on open and close of the workbook.

这消除了在工作簿打开时隐藏功能区的机会，然后用户手动显示功能区，这反过来会在关闭时反转显示并实际隐藏功能区。打开时，功能区将再次显示。这将在每次打开和关闭工作簿时保持不变。

回答by Brian

Give this a try:

试试这个：

Sub ShowHideRibbon()

If CommandBars("Ribbon").Controls(1).Height < 100 Then
    CommandBars.ExecuteMso ("MinimizeRibbon")
Else
    CommandBars.ExecuteMso ("MinimizeRibbon")
End If

End Sub

Or this:

或这个：

Sub ShowHideRibbon1()

If Application.ExecuteExcel4Macro("Get.ToolBar(7,""Ribbon"")") Then
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", False)"
Else
    Application.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"", True)"
End If

End Sub

回答by BruceWayne

First, go to the Excel Options, and go to "Quick Action Toolbar".

首先，转到 Excel 选项，然后转到“快速操作工具栏”。

From there, search for "Hide Ribbon" and add to the toolbar. After it's on the QAT, you can call it quickly with ALT+# (on my computer it's the 8th thing, so ALT+8 will auto-hide).

从那里，搜索“隐藏功能区”并添加到工具栏。在 QAT 上之后，你可以用 ALT+# 快速调用它（在我的电脑上它是第 8 件事，所以 ALT+8 会自动隐藏）。

Then just add a sub that does SendKeys ALT then 8:

然后只需添加一个执行 SendKeys ALT 然后 8 的子：

Sub Macro1()
ActiveSheet.Activate
'Cells(1, 1).Select
SendKeys "%0", True
SendKeys "8", True

End Sub

Note: I know it's silly to have ActiveSheet.Activate, I just added that to test the macro. Depending on how it's being called, you can remove/comment out that line. The %is equivalent to ALT, and technically, I have to press 0 then 8, hence the two lines.

注意：我知道拥有 很愚蠢ActiveSheet.Activate，我只是添加了它来测试宏。根据它的调用方式，您可以删除/注释掉该行。的%相当于ALT，并在技术上，我必须按0然后如图8所示，因此，两行。

回答by PGD15

To get this code to work in excel 2016 you will need the following code in the "ThisWorkbook" mode.

要使此代码在 excel 2016 中工作，您需要在“ThisWorkbook”模式下使用以下代码。

Credit goes to BigBen - not me

归功于 BigBen - 而不是我

Private Sub Workbook_Open()
application.CommandBars.ExecuteMso "HideRibbon"
End Sub