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

 

[Lewzerrrr]

I'm not too sure, I'll still need to use the above code and especially the For Each Sh in Arr..

So I'd need to store the sheets that have X in their name into the Arr no?

 

[RoryA]

Why do you need the For each sh in arr?

 

[Lewzerrrr]

Because I’m unsure of an alternate piece of code that lets me hide and unhide the sheets in one button with a password prompt

 

[RoryA]

Perhaps we can start from the beginning. What exactly is it that you want to happen?

 

[Lewzerrrr]

I have a load of sheets that contain the word “Daily” that I would like to hide and unhide with one button which has a password prompt on unhiding.

The code originally was perfect but the sheets were hard coded so if I was to remove or add another daily sheet, it wouldn’t capture it and would break.

 

[RoryA]

Try something like this (air code):

Private Sub HideUnAdminSheets()

    Dim Password As String: Password = "******"
    Application.ScreenUpdating = False
    Sheet1.Activate
    If AllHidden Then
Retry:
        If LCase(InputBox("Please enter the admin password.")) = Password Then
            Call LoopSheets(xlSheetVisible)
        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
if instr(1, sh.name, "Daily", vbtextcompare) <> 0 then sh.visible = IsVisible
next
End Sub
Private Function AllHidden() as boolean
AllHidden = True
for each sh in sheets
if instr(1, sh.name, "Daily", vbtextcompare) <> 0 then
    If sh.visible <> xlsheetveryhidden then
        allhidden = false
        exit for
    end if
next
End Function