Hiding menu items in specified sheets

[TheJay]

Hello there,

I have code in "ThisWorkbook" which works only when one sheet is specified, but I'd like it to apply to all those specified and not to any others.

So far, this is what I have got and have tried:

Option Explicit
'Hide all menus and horizontal scrollbar
Sub hide_menu()

'With Worksheets("HA" Or "Ll" Or "Dev")
With Worksheets("HA")

    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
  
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .CommandBars("Standard").Visible = False
        .CommandBars("Formatting").Visible = False
    End With
End With
End Sub
'Unhide revelant menus
Sub unhide_menu()

'With Worksheets("HA" Or "Ll" Or "Dev")
With Worksheets("HA")

    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
    With Application
        .DisplayFullScreen = False
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With
    With Application
        .CommandBars("Full Screen").Visible = True
        .CommandBars("Worksheet Menu Bar").Enabled = True
        .CommandBars("Standard").Visible = True
        .CommandBars("Formatting").Visible = True
    End With
End With
End Sub

 

[Siddharth Rout]

Since you are dealing with ActiveWindow and Application wide properties, use the Workbook_SheetActivate event. This code goes in the ThisWorkbook code module.

PS: This is basic example. Feel free to adapt it to use your Hide and UnHide code accordingly.

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.Name
        Case "HA", "Ll", "Dev"
            With ActiveWindow
                .DisplayHorizontalScrollBar = False
                .DisplayVerticalScrollBar = True
            End With
           
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
               
                .CommandBars("Full Screen").Visible = False
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
        Case Else
            ActiveWindow.DisplayHorizontalScrollBar = True
           
            With Application
                .DisplayFullScreen = False
               
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
               
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
    End Select
End Sub

 

[TheJay]

Thank you, so to replicate the desired behavior:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Select Case Sh.Name
        Case "HA", "Ll", "Dev"
            With ActiveWindow
                .DisplayHorizontalScrollBar = False
                .DisplayVerticalScrollBar = True
            End With
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = False
                .DisplayStatusBar = False
                .CommandBars("Full Screen").Visible = False
                .CommandBars("Worksheet Menu Bar").Enabled = False
                .CommandBars("Standard").Visible = False
                .CommandBars("Formatting").Visible = False
            End With
        Case Else
            ActiveWindow.DisplayHorizontalScrollBar = True
            ActiveWindow.DisplayVerticalScrollBar = True
            With Application
                .DisplayFullScreen = True
                .DisplayFormulaBar = True
                .DisplayStatusBar = True
                .CommandBars("Full Screen").Visible = True
                .CommandBars("Worksheet Menu Bar").Enabled = True
                .CommandBars("Standard").Visible = True
                .CommandBars("Formatting").Visible = True
            End With
    End Select
End Sub