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

[sherriwatkins]

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!

 

[Logit]

.
Paste this in ThisWorkbook module :

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 :

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


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

 

[sherriwatkins]

Worked like a charm!! Thank you so much for your help!

 

[Logit]

You are welcome.