VBA code asking for Macro

[KMH]

I placed in this code in ThisWorkbook in an attempt to have it remove all Toolbars and Menus from Sheet2 only, but it's triggering the Macro Name prompt. How do I fix that. It's just supposed to run automatically without a Macro. There is also a bit of code above that to allow the Macro buttons on the other sheets to work when the worksheet is protected. The bit I need sorted out is the one that starts with - Private Sub Workbook_SheetActivate(ByVal Sh As Object)

I should also point out that Sheet2 is named COVER. Should I be referring to Sheet2 or COVER in the code?

Private Sub Workbook_Open()

Call AllowMacroWhenProtected

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim ws As Worksheet

If ActiveSheet.Name = "Sheet2" Then
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Visible = False
Application.CommandBars("Formatting").Visible = False
Application.CommandBars("Custom 1").Visible = False
With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
    End With
Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False
Else
Application.CommandBars("Worksheet Menu Bar").Enabled = True
Application.CommandBars("Standard").Visible = True
Application.CommandBars("Formatting").Visible = True
Application.CommandBars("Custom 1").Visible = True
With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With
Application.DisplayFormulaBar = True
Application.DisplayStatusBar = True
End If
 
Application.DisplayAlerts = True
End Sub

Many thanks in advance for your help!

 

[Michael M]

Why not simply put it this into the "COVER" sheet module.

Private Sub Worksheet_Activate()
Dim ws As Worksheet
If ActiveSheet.Name = "COVER" Then.........the rest of the code

 

[KMH]

I've attached a picture of my screen, if that helps any

 

[KMH]

Why not simply put it this into the "COVER" sheet module.

Private Sub Worksheet_Activate()
Dim ws As Worksheet
If ActiveSheet.Name = "COVER" Then.........the rest of the code

Changed the name of the worksheet from Sheet2 to COVER as suggested, and got this error:

 

[Michael M]

did you move it to the sheet module ??

 

[KMH]

did you move it to the sheet module ??

No. I just edited it where it was - in ThisWorkbook

 

[Michael M]

It needs to be in the sheet module so that when the sheet is activated, to code will run

 

[KMH]

It needs to be in the sheet module so that when the sheet is activated, to code will run

I moved it to the Sheet2 (COVER) and still got the error:

I clicked to debug it, and this is the line highlighted:

 

[Michael M]

Check to make sure the CommandBar is called "Custom 1"
I think it will either need an underscore OR No space
So, either "Custom_1" OR "Custom1"

 

[KMH]

Thanks for your time and energy with that, but nothing I try is working. Is there an easier way to make the entire workbook show in full screen but still see the worksheet tabs?