Error when using password to hide tabs

[Hooqa13]

I am trying to create a spreadsheet that people can use one password to show all hidden sheets in the workbook and another password that only shows 2 of the hidden sheets. This 2nd password is very important so that users cannot see the hidden tabs or any of the information on those tabs. The VBA code I have works for the first password and shows all hidden sheets but I get an error when I try to use the 2nd limited password.

The error I receive is Run-time error '32809': Application-defined or object-defined error

The VBA code I have linked to a button is below. Range D1 refers to the password "secure" and Range D2 refers to the password "VP". It's the VP password that is not working...

Private Sub Password_Click()

'Opens the tool based on password given in instruction tab-----------------------------

Dim msg
    
    Application.ScreenUpdating = False
    
    Select Case PasswordBox.Text
    
'VP Access Password

    Case Sheets("Security").Range("D1").Text
        PasswordBox.Text = ""
        
        Sheets("Instructions").Visible = True
        Sheets("Budget Analysis").Visible = True
        Sheets("Position Review").Visible = True
        ActiveWorkbook.Protect Password:="secure", Structure:=True, Windows:=False
             
'Admin Access Password

    Case Sheets("Security").Range("D2").Text
        PasswordBox.Text = ""
        
        ActiveWorkbook.Unprotect Password:="secure"
        Dim ws As Worksheet
            For Each ws In Worksheets
            ws.Visible = True
            ws.Unprotect Password:="secure"
            Next

'Hide

    Case "hide"
        PasswordBox.Text = ""

        ActiveWorkbook.Unprotect Password:="secure"
        Dim ws1 As Worksheet
        Dim AER As AllowEditRange
            For Each ws1 In Worksheets
            If ws1.Visible = xlSheetVisible Then ws1.Select
            ws1.Unprotect Password:="secure"
            For Each AER In ws1.Protection.AllowEditRanges
            AER.Delete
            Next AER
            If ws1.Name <> Sheets("Instructions").Name Then ws1.Visible = xlSheetVeryHidden
            Next ws1
            
    End Select
    
Application.ScreenUpdating = True
    
End Sub

 

[craig.penny]

You may need to add

ActiveWorkbook.Unprotect Password:="secure"

to the VP case.

 

[Hooqa13]

Just tried it and got the same error...

 

[craig.penny]

Which line is highlighted when the code stops?

 

[Hooqa13]

Sheets("Budget Analysis").Visible = True

 

[craig.penny]

I know this sounds silly but make sure the spelling on the tab name doesn't have an extra space in between or at the end and the letters are identical.

 

[craig.penny]

Did you get it working?

 

[Hooqa13]

No and I triple checked the spelling thinking that might have been it too! :sigh: I'm going to re-create the entire thing, maybe the file got corrupted somehow but please let me know if you have more suggestions

 

[craig.penny]

Hi Hooqa13,

Did you get it working? I'm curious to know the problem.