VBA: Use password to go to sheet

Report
Posts
1
Registration date
Saturday June 19, 2021
Status
Member
Last seen
June 19, 2021
-
Posts
2774
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 13, 2021
-
Hi, I have a spreadsheet that is driven by VBA buttons that take you to different sheets, I need to password protect some of the sheets, but if an incorrect code is put in still takes you to the Sheet, also if you cancel or don't put a code it takes you to the Sheet, how do I correct? below I have detailed the code.

Private Sub CommandButton8_Click()

Dim password As Variant
password = Application.InputBox("Enter Password", "Password Protected")
Select Case password
Case Is = False
'do nothing
Case Is = "Shewasadragon"
Range("A1").Value = "Shewasadragon"
Case Else
MsgBox "Incorrect Password"
End Select
Sheets("Bank Account").Select

End Sub

1 reply

Posts
2774
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 13, 2021
465
Hi Andy,

You end your code by going to the sheet, so you will always get there. Try placing 'Sheets("Bank Account").Select' before before 'Case Else'.

Or use the code below


Private Sub CommandButton8_Click() 
Dim mPW As String
mPW = InputBox("Enter Password", "Password Protected")
If mPW = "Shewasadragon" Then
    Sheets("Bank Account").Select
Else
    MsgBox "Incorrect Password"
End If
End Sub


I don't see anything about unprotecting the sheet, but I guess you'll add that later.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!