Show Hide Ribbon Excel Vba

Show the ribbon

The Ribbon has multiple display options to fit your preferences, but with an errant click, you can unintentionally hide your Ribbon.

To quickly show the Ribbon, click any tab, for example, the Home or Insert tab.

To show the Ribbon all the time, click the arrow (Excel 2013) or pushpin icon (Excel 2016) on the lower-right corner of the Ribbon.

For more control of the Ribbon, you can change your view and maximize the Ribbon by accessing the Ribbon Display Options near the top of your Excel document.

Using the Ribbon Display Options

Click the Ribbon Display Options icon on the top-right corner of your document. It is to the left of the Minimize icon.

In the menu that opens, click Show Tabs and Commands to show the Ribbon with all tabs and full commands.

This option is the default view. While this option provides quick access to all the commands, it limits the available screen space for your workbook.

Tip: Press Ctrl+F1 to show and hide your commands in the Ribbon.

Click Show Tabs to display the Ribbon tabs without the commands. To access the commands in the Show Tabs option, click any of the tabs.

Click Auto-hide Ribbon to hide all tabs and commands.

By using this option, you get the largest amount of screen space when you view your workbook. To access tabs and commands in this view, click the very top of your workbook.

Tip: You can customize the Ribbon with your own tabs and commands for quick access to the toolbar features you use most.

VBA auto hide ribbon in Excel 2013

How to Auto-hide Ribbon in 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:

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

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

Attempt 1

This is good but hides the blue icon.

Attempt 2

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 .

Attempt 3

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

7 Answers 7

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 .

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

Running the following code:

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 Ribbon button manually):

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

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

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:

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 Hide/Show all tabs on Ribbon except custom tab

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.

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

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

6 Answers 6

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

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

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

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.

What you can do with the Ribbon using 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.

What you can’t do with the Ribbon using 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

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

VBA: Hide ribbon & others, how to limit to one workbook?

MrsAlice

New Member

The issue
On opening the file my code hides the:

  • ribbon
  • formula bar
  • status bar
  • tabs

When the file is closed it turns the aforementioned back on. This all works fine until you start working in several excel files simultaneously: the ribbon etc. are hidden on all open excel files.

Is it possible to limit the effects of the code to this single workbook in the VBA code itself? I can only think of a workaround by opening this file in a separate Excel-instance, but this has some serious disadvantages. Workbook_Activate & Workbook_Deactivate looked promising, but can’t get it to work.

The current code on opening(some code omitted, it is in ThisWorkbook)

Some context
– I have scoured the web for people asking similar questions, got some results, but no real answer.
– I have made passive use of MrExcel and Ozgrid for a lot of issues, but this is my first post. I hope I am doing things right. This is not being crossposted. I will check up on this topic frequently.

Thank you for your time. Without communities such as these I would not be able to learn so much about Excel and VBA as I am now.

Some videos you may like

Excel Facts

Andy Pope

Board Regular, The other chart guy

You also need to add code to the Activate and deactivate events.

MrsAlice

New Member

Thanks for the response Andy Pope.

I have previously tried to simply switch from Workbook_Open/close to Workbook_Activate/Deactivate, but to no avail. The code in the opening post is simply the code I am currently using, not something I was trying Sorry for the confusion.

When I switch as mentioned before (I also removed the Boolean declaration and remove the setRange / select.Myrange in both subs) it simply bounces back to the file that contains this code, without turning the interface back on in the other workbooks.

To rephrase my question: Below is the complete code that I am currently using to hide the Excel interface for 1 single workbook. It also hides the interface in other workbooks. How do I prevent this from happening? Are Workbook_Activate & Deactivate the right tools for the job? If not, what is? Is it possible at all without opening a separate Excel instance?

Andy Pope

Board Regular, The other chart guy

MrsAlice

New Member

[Solved]
It works now. A minor nuisance is that once in a while the screen I am switching to (including programs like Firefox) turns completely black. It returns to normal after switching once or twice, so it’s no biggy.

The combination of the code in my 2nd post and this one is probably suboptimal, but it works.

The code that did it, for future generations :

guigui971

New Member

I come here with a problem related to it.
I have a workbook with multiple sheets, for which I want to leave EXCEL appear as little as possible, because the user only need direct input into the sheets, without the need of different menus EXCEL, and because I want to use maximum screen.

During the use of this "special" workbook, it may be necessary to use another, who needs the aspect of "normal" EXCEL .

I created different events placed in "ThisWorkbook" supposed to do all this:

Sub Workbook_Open()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Application.DisplayFullScreen = True
Application.DisplayStatusBar = False‘(here I hesitate because some, like here, put "Not Application.DisplayStatusBar" instead of "False")
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Sub Workbook_activate()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayGridlines = False
Application.DisplayFullScreen = True
Application.DisplayStatusBar = False
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayFullScreen = False
ActiveWindow.View = xlNormalView
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
Application.DisplayFullScreen = False
Application.ScreenUpdating = True
End Sub

Private Sub Workbook_Deactivate()
Application.ScreenUpdating = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
Application.DisplayFormulaBar = True
ActiveWindow.View = xlNormalView
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayGridlines = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True
Application.DisplayFullScreen = False
Application.ScreenUpdating = True
End Sub

The problem is this code does not work every time:
– When I switch to another workbook ("normal"), the ribbon and the formula bar stay usually (not always!) hidden, while header rows / column, and the grid are appearing.
– When I pass from one sheet to another on my "special" workbook, the formula bar sometimes returns.
– Same when I go back from my "normal" workbook to my "special" workbook, the formula bar and / or header rows / column sometimes reappear .

I tried to slow down my code by adding a small loop between each line: no effect!

And I did not find any answer in the web, except here.

In short, after having tried everything, I’m lost!

Comments: