How to auto hide ribbon but only for a specific workbook and only for one worksheet in that workbook

Question

How to auto hide ribbon but only for a specific workbook and only for one worksheet in that workbook when workbook opens?

When I use the below code is working as expected and hide the ribbon but when I open another excel files the ribbon in the files is also hidden.

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

What about showing the ribbon again from the workbook deactivate event? You may just have to place your logic in multiple places. — Skin
Thank you for the suggestion. I have added the code to Workbook_Deactivate and Workbook_Activate and it sort of working, however when I started opening multiple sheets it losing its sequence. I am definitely on the right path :D — Michal
yeah, sounds like it. You just gotta work through the scenarios. — Skin

dimitris dimitris · Accepted Answer · 2019-04-12T09:04:32

Name the worksheet in a unique way e.g."aaa" and then write inside the ThisWorkbook module the following:

Private Sub Workbook_Activate()
    If ActiveSheet.Name = "aaa" Then
        'Application.CommandBars.ExecuteMso "HideRibbon"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" 'code to hide ribbon.
    Else
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" 'code to show ribbon.
    End If
End Sub

Private Sub Workbook_Deactivate()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" 'code to show ribbon.
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = "aaa" Then
        'Application.CommandBars.ExecuteMso "HideRibbon"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" 'code to hide ribbon.
    Else
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" 'code to show ribbon.
    End If
End Sub

which is tested in Excel 2007 and Excel 2010 and works fine.

By the way, having tested Application.CommandBars.ExecuteMso "HideRibbon" in Excel 2007 and Excel 2010, throws an error, which version do you use?

If you prefer, replace the above 'hide/show lines', with those that work in your version according to the comments.

How to auto hide ribbon but only for a specific workbook and only for one worksheet in that workbook

1 Answers