Hide Menu Ribbon Only this Workbook

[NextYearSox]

I am using the following VBA code to hide/unhide the menu ribbon. Is there a way that I can restrict this to only the one workbook? Any other open EXCEL workbooks also have their menus hidden ... ugh

Private Sub Autpen()
Application.ScreenUpdating = False

Application.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",False)"
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = Not Application.DisplayStatusBar
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.DisplayVerticalScrollBar = False

End Sub

Private Sub Auto_Close()

Application.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"",True)"
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
ActiveWindow.DisplayVerticalScrollBar = True

End Sub

 

[Domenic]

Instead of using the Autpen and Auto_Close events, use the Workbook_Activate and Workbook_Deactivate events...

Private Sub Workbook_Activate()
    '
    '
    '
End Sub

Private Sub Workbook_Deactivate()
    '
    '
    '
End Sub

Hope this helps!

 

[NextYearSox]

I tried this and can't get it to work. I am calling these from a Module, and when I launch the file the subroutine doesn't execute. If I put these into ThisWorkbook, the subroutine's launch, but then I can't select another workbook. Everytime I try and do so, the subroutine executes again.

Am I missing something?

 

[Domenic]

The Workbook_Activate and Workbook_Deactivate events should go in the code module for ThisWorkbook, and should work like this...

1) When the workbook is opened, the Workbook_Activate event is triggered, and the ribbon is hidden, etc.

2) When another workbook is either opened or activated, the Workbook_Deactivate event from the first workbook is triggered, and the ribbon is shown.

3) And if the first workbook is activated, the Workbook_Activate event is triggered, and the ribbon is hidden again.

4) And if the workbook is closed, the Workbook_Deactivate event is triggered, and the ribbon is shown.

Isn't this what you're looking for?

 

[NextYearSox]

This process didn't work initially. I monkeyed around with my activate/deactivate subroutines and discovered that the following was monkeying everything up:

On Workbook_Activate ...
For Each WorksheetTab In Worksheets
WorksheetTab.Activate
ActiveWindow.DisplayHeadings = False
Next WorksheetTab

On Workbook Deactivate ...
For Each WorksheetTab In Worksheets
WorksheetTab.Activate
ActiveWindow.DisplayHeadings = False
Next WorksheetTab

Once I commented these out, everything functioned as you described ... thank you.

The purpose of the preceding was to hide (unhide) the column and Row headers. Is there an easer way to accomplish this?

 

[Domenic]

For the display headings, why not simply set it manually? Otherwise, since the settings are workbook specific, try placing them in the Workbook_Open and Workbook_BeforeClose events.

 

[NextYearSox]

Perfect .... Thank you

 

[Domenic]

You're very welcome!