VBA Code for hiding ribbon not working 100% of the time

sherriwatkins

New Member
Joined
Jun 10, 2015
Messages
10
Hi,

I have written code to hide the ribbon when opening a workbook. The problem I have is two fold...

1. When you open the workbook the first time, it works like a charm, but if you close the file and reopen it, the ribbon doesn't hide like it should.

2. Even though the code is written in one workbook, it is hiding the ribbon on other workbooks that I open later.

Here is my code:

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

MsgBox ("Click arrow in top right corner to see Ribbon Bar and select Show Tabs and Commands.")

End Sub

What am I doing wrong??


Thanks in advance for your help!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,948
.
Paste this in ThisWorkbook module :

Code:
Option Explicit


Private Sub Workbook_Open()
    'These commands disable the menu bar.
   ' Application.DisplayFormulaBar = False
   ' Application.DisplayStatusBar = False
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'These commands re-enable the menu bar so other workbooks
    'won't be affected.
    'Application.DisplayFormulaBar = True
    'Application.DisplayStatusBar = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub


Then if you want to show or hide the menu bar after the workbook is opened, paste this into a regular module and activate with command buttons :

Code:
Option Explicit
Sub shwMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"


End Sub
Sub hideMenu()
 Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,723
Members
415,853
Latest member
Newlife72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top