Hiding menu items in specified sheets

TheJay

Active Member
Joined
Nov 12, 2014
Messages
364
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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.

VBA Code:
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

1661248198614.png
 
Upvote 0
Solution
Thank you, so to replicate the desired behavior:

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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
Back
Top