CODEWITHSUNDEEP
excelvbaautomationprivate
AND AND AND
AND AND AND

Reputation: 3

How do you call multiple Sheet.Names in VBA in an "IF statement?

In the following code every time I click on the worksheet "Cover" it hides the ribbon and zooms into a specific set of cells. I want to keep this code but also hide the ribbon for another worksheet called "Homepage". I have tried to do an Or statement in the IF statement and that doesn't work. Any help would be great. :D

    If ActiveSheet.Name = "Cover" Then
        'Application.CommandBars.ExecuteMso "HideRibbon"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" 'code to hide ribbon.
        Range("C4:S51").Select
        ActiveWindow.Zoom = True
        Range("a5").Select
        Application.DisplayFormulaBar = False
    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 = "Cover" Then
        'Application.CommandBars.ExecuteMso "HideRibbon"
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" 'code to hide ribbon.
        Range("C4:S51").Select
        ActiveWindow.Zoom = True
        Range("a5").Select
        Application.DisplayFormulaBar = False
    Else
        Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)" 'code to show ribbon.
    End If
End Sub```

Upvotes: 0

Views: 669

Answers (1)

braX
braX

Reputation: 11755

To use Select Case (which is a much cleaner way to do it, making it much more readable)

Select Case ActiveSheet.Name 
  Case "Cover", "Homepage", "Cover3"
    ' do the same thing for all 3 of these
  Case "AnotherName"
    ' do something
  Case Else
    ' do something else
    MsgBox "This sheet name is wrong!"
End Select

Upvotes: 1

Related Questions