Hide all sheets except main sheet

[ABabeNChrist]

I have a workbook with many sheets, some may maybe visible and some maybe hidden, it varies from report to report. I was trying to come up with a code that will hide all sheets except for sheet named “_”
I’ll use this to reset my reports back to hidden
Sheets “_” is the main sheet and could possibly be hidden.
So I thought of maybe trying something like this

Code: Select all

    Dim wssheet As Worksheet
    Application.ScreenUpdating = False

    ActiveWorkbook.Protect PassWord:="", Structure:=False, Windows:=False
    
    Worksheets("_").Visible = xlSheetVisible
    
    For Each wssheet In ThisWorkbook.Worksheets
        If Not wssheet.Name = "_" Then wssheet.Visible = False
    Next wssheet

    Application.ScreenUpdating = True

    ActiveWorkbook.Protect PassWord:="", Structure:=True, Windows:=True

should this line be

Code: Select all

If Not wssheet.Name = "_" Then wssheet.Visible = False

or should it be this

Code: Select all

If Not wssheet.Name = "_" Then wssheet.Visible = xlSheetHidden

[HansV]

You can use False or xlSheetHidden. It doesn't matter. They are exactly the same value: 0.
Similarly, True and xlSheetVisible are the same too: -1.

[ABabeNChrist]

Thank you Hans
I wasn’t sure of the correct approach they both seemed worked just fine and since they have the same value that explains it….

[ABabeNChrist]

I was thinking of adding a Yes and No option.
Also to save changes if yes is selected.
Once again I’m just checking to see if I set it up correctly.

Code: Select all

    Dim Answer As String
    Dim MyNote As String

    Application.ScreenUpdating = False

    MyNote = "Would you like to reset and save changes to updated report(s)"

    Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Updating Reports")

    If Answer = vbNo Then
        MsgBox "You may now continue"

    Else
        Dim wssheet As Worksheet

        ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

        Worksheets("_").Visible = xlSheetVisible

        For Each wssheet In ThisWorkbook.Worksheets
            If Not wssheet.Name = "_" Then wssheet.Visible = False
        Next wssheet

        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

        ThisWorkbook.Save

        MsgBox "Updated report(s) are now reset and saved, you may now continue"

    End If

    Application.ScreenUpdating = True

[HansV]

That looks OK.