VBA custom password protecting for more than 1 sheet

Question

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

Scott Holtzman · Accepted Answer · 2018-11-21 17:55:29Z

3

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

edited Nov 21, 2018 at 17:55

answered Nov 20, 2018 at 17:17

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Macca424 Over a year ago

Thanks for the reply, your code works perfectly when putting in the right password, however if you put in the wrong password it seems to delete the sheet entirely, even when closing and reopening, it is still not there. I've had to load a backup and test it a few times. I also would like to scale this to the 9 or so sheets I have in the document, which won't all contain the string cascada in them. Thanks.

Scott Holtzman Over a year ago

see now. i made a small change. there's no need to hide/unhide the sheet

Macca424 Over a year ago

Ok thanks, see edited OP. I added one piece to your code which redirects me to a "home" page if they put in the incorrect password, the last thing i want to do now is scale this to the 9 or so sheets i mentionned in my opening post. For example I could have another tab called "config_KitDrws" which I want the password to be "KitDrws" for. What would be the best way to do this? To separate out my case statements and add each password that I want?

Macca424 Over a year ago

I answered myself a bit, see OP for solution to this, not sure if it's best practice but it works well.

Scott Holtzman Over a year ago

@Macca424 - by doing what you did you butchered the entire SO experience. You asked a question then edit your question with your own answer. That is not at all helpful to someone coming behind you. On top of that you added features to the original question, thus essentially, adding a question. Lastly, if the answer provided answered your question, it's best practice to mark it as answered.

|

Collectives™ on Stack Overflow

VBA custom password protecting for more than 1 sheet

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related