CODEWITHSUNDEEP
excelvba
Macca424
Macca424

Reputation: 65

VBA custom password protecting for more than 1 sheet

I'm currently working on a macro enabled excel sheet, with multiple tabs (there are 9 tabs I would like to do this on, but for the purposes of the question I'll include only 2), and for each tab I would like to add a password prompt that matches what I specify in the code.

This is working ok, but my issue is that when two sheets are located next to each other on the actual worksheets tab, it will go through them both rather than hiding the first one until i input the correct password.

For example, on my sheet I have a tab named Cascada, followed by a tab named Cascada2. If I were to put a blank tab inbetween these two, then my code would work correctly. However when they are in sequence, it seems to go through the sequence of password prompts regardless of whether I input the correct string or not.

See code below, any advice would be appreciated.

Thanks.

EDIT UPDATED WITH ANSWER

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Application.EnableEvents = False

Dim cascada As String, cascada2 As String
cascada = "Config_Cascada"
Rhea = "Config_Rhea"

Select Case Sh.Name
    Case cascada, cascada2

        Dim pwd As String
        pwd = "cascada" & IIf(Sh.Name = cascada2, 2, "")

        Dim Response As String
        Response = InputBox("Enter password to view sheet")

        If Response = pwd Then
            Sh.Select
        Else
        Worksheets("Doors").Activate
        End If


End Select

Select Case Sh.Name
    Case Rhea

        Dim pwdRhea As String
        pwdRhea = "rhea"

        Dim ResponseRhea As String
        ResponseRhea = InputBox("Enter password to view sheet")

        If Response = pwdRhea Then
            Sh.Select
        Else
        Worksheets("Doors").Activate
        End If


End Select

Application.EnableEvents = True

End Sub

Upvotes: 0

Views: 111

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

Give this a shot. Cleaner and works as far as I tested:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    Application.EnableEvents = False

    Dim cascada As String, cascada2 As String
    cascada = "config_Cascada"
    cascada2 = "config_Cascada2"

    Select Case Sh.Name
        Case cascada, cascada2

            Dim pwd As String
            pwd = "cascada" & IIf(Sh.Name = cascada2, 2, "")

            Dim Response as String
            Response = InputBox("Enter password to view sheet")

            If Response = pwd Then
                Sh.Select
            End If

    End Select

    Application.EnableEvents = True

 End Sub

Upvotes: 3

Related Questions