Hide Unhide Specific Sheets

[Lewzerrrr]

Hi,

I created the below code a few months ago but just wondering how I could make it shorter?

The premise of it is that you enter the admin password and it will hide 6 admin sheets using their codenames.

This is then assigned to a button in my workbook and it will hide and unhide.

Is there a way to assign the codenames to an array variable? This will make updating easier if I need to add a new sheet or delete.

Also, to decide on opening or closing, I pass it through an IF statement in the line below. Can I use an Array(Codenames).count to switch the 6?

For Each Sh In Sheets(Array(Sheet5.Name, Sheet12.Name, Sheet2.Name, Sheet3.Name, Sheet33.Name, Sheet34.Name))

    If Sh.Visible = xlSheetVeryHidden Then I = I + 1


Next Sh


If I = 6 Then

Full code:

Private Sub HideUnAdminSheets()

Dim Password As String, PasswordEnter As String


Password = "******"


Application.ScreenUpdating = False


I = 0


For Each Sh In Sheets(Array(Sheet5.Name, Sheet12.Name, Sheet2.Name, Sheet3.Name, Sheet33.Name, Sheet34.Name))


    If Sh.Visible = xlSheetVeryHidden Then I = I + 1


Next Sh


If I = 6 Then


Retry:
PasswordEnter = InputBox("Please enter the admin password.")


    Select Case LCase(PasswordEnter)
    
        Case LCase(Password)
        
            For Each Sh In Sheets(Array(Sheet5.Name, Sheet12.Name, Sheet2.Name, Sheet3.Name, Sheet33.Name, Sheet34.Name))
            
                Sh.Visible = True
            
            Next Sh
            
            Sheet1.Activate
            
        Case Else
        
            RetryPassword = MsgBox("Would you like to retry?", vbYesNo)
            
                Select Case RetryPassword
                
                    Case vbYes
                    
                        GoTo Retry
                    
                    Case vbNo
                    
                        Exit Sub
                    
                End Select
        
    End Select
    
Else


    For Each Sh In Sheets(Array(Sheet5.Name, Sheet12.Name, Sheet2.Name, Sheet3.Name, Sheet33.Name, Sheet34.Name))
    
        Sh.Visible = xlSheetVeryHidden
    
    Next Sh
    
    Sheet1.Activate
    
End If


Application.ScreenUpdating = True


End Sub

 

[Yongle]

Try this
- place code in sheet module of sheet with command button (I assume this is sheet1)
- declaration of arr must be above all procedures

Private [COLOR=#ff0000]arr
[/COLOR]
Private Sub CommandButton1_Click()
    Call HideUnAdminSheets
End Sub

Private Sub HideUnAdminSheets()

    Dim Password As String: Password = "******"
    Application.ScreenUpdating = False
    Sheet1.Activate
    I = 0

    arr = Array(Sheet5.Name, Sheet12.Name, Sheet2.Name, Sheet3.Name, Sheet33.Name, Sheet34.Name)
        For Each sh In Sheets(arr)
            If sh.Visible = xlSheetVeryHidden Then I = I + 1
        Next sh

    If I = 6 Then

Retry:
        If LCase(InputBox("Please enter the admin password.")) = Password Then
            Call LoopSheets(True)
        Else
            If MsgBox("Would you like to retry?", vbYesNo) = vbYes Then GoTo Retry Else Exit Sub
        End If
    
    Else
        Call LoopSheets(xlSheetVeryHidden)
    End If

    Application.ScreenUpdating = True

End Sub

Private Sub LoopSheets(IsVisible)
    For Each sh In Sheets(arr)
        sh.Visible = IsVisible
    Next sh
End Sub

 

[Lewzerrrr]

Neat!

 

[Lewzerrrr]

Just to update, I replaced the hard coded "6" with uBound(Arr) + 1

 

[Yongle]

slighly neater version avoiding use of sheet names
array now is an array of sheets (previously array of names)
3 lines affected
your amendment built-in

Private arr

Private Sub CommandButton1_Click()
    Call HideUnAdminSheets
End Sub

Private Sub HideUnAdminSheets()

    Dim Password As String: Password = "******"
    Application.ScreenUpdating = False
    Sheet1.Activate
    I = 0
    [COLOR=#ff0000]arr = Array(Sheet5, Sheet12, Sheet2, Sheet3, Sheet33, Sheet34)[/COLOR]
    
        [COLOR=#ff0000]For Each sh In arr[/COLOR]
            If sh.Visible = xlSheetVeryHidden Then I = I + 1
        Next sh

    If I = UBound(arr) + 1 Then

Retry:
        If LCase(InputBox("Please enter the admin password.")) = Password Then
            Call LoopSheets(True)
        Else
            If MsgBox("Would you like to retry?", vbYesNo) = vbYes Then GoTo Retry Else Exit Sub
        End If
    
    Else
        Call LoopSheets(xlSheetVeryHidden)
    End If

    Application.ScreenUpdating = True

End Sub

Private Sub LoopSheets(IsVisible)
    [COLOR=#ff0000]For Each sh In arr[/COLOR]
        sh.Visible = IsVisible
    Next sh
End Sub

 

[Lewzerrrr]

I like this bit especially, the annoyance of typing out .Name (1st world problems)

arr = Array(Sheet5, Sheet12, Sheet2, Sheet3, Sheet33, Sheet34)

 

[Lewzerrrr]

Sorry to gravedig, instead of hard coding the sheets, is it possible to hide sheets that contain X in their sheet names?

 

[RoryA]

Basically this:

for each sh in sheets
if instr(1, sh.name, "x", vbtextcompare) <> 0 then sh.visible = xlsheethidden
next

 

[Lewzerrrr]

Basically this:

for each sh in sheets
if instr(1, sh.name, "x", vbtextcompare) <> 0 then sh.visible = xlsheethidden
next

How can I incorporate this into the above code in thread? Preferably I need to assign it to Arr?

 

[RoryA]

Why do you need arr? I thought the whole point was to avoid hardcoding names.