VBA Help on Password Protected Tab

[Tennisball]

I have created a macro to protect a tab/sheet with a password. I am wondering how I can modify the macros so that once the password is entered correctly the first time, the macro does not keep asking to enter a password. I want it so that it only gets prompted the first time the sheet is activated and when the password is entered incorrectly. Any ideas?

Private Sub Worksheet_Activate()
Dim Password
Password = Application.InputBox("Please enter a valid password.")
If Password <> "pword" Then
MsgBox "This is not for you to view, please do not try to access this sheet!"
Sheets("Sheet1").Select
If pword <> "pword" Then Sheets("Sheet2").Visible = False
Else
ActiveSheet.Unprotect Password
End If
End Sub

 

[bobsan42]

if the password should be stored only for the current file ssession - create a variable in the begining of your code module before any Subs or Functions
when properly entered - store the password to it - so if it is NOT Nothing use it instead of prompting again.
if you want the password to live between sessions - it is another story.
btw - where do you store the password?

 

[Tennisball]

I am sorry, I am a novice at VBA and don't really understand a word of what your wrote. As for the password, the password is stored in the script. the password is just "pword"

 

[Michael M]

Hi
You could hide Sheet2 with a Workbook_open event
and then use

Private Sub Worksheet_Activate()
Dim Password As String
msg = MsgBox("Do you need to open Sheet2", vbYesNo)
If msg = vbYes Then
Password = Application.InputBox("Please enter a valid password.")
Else: Exit Sub
End If
If Password <> "pword" Then
MsgBox "This is not for you to view, please do not try to access this sheet!"
exit sub
Else
Sheets("Sheet2").Visible = True
End If
End Sub

 

[Tennisball]

Thanks Michael M, but your code doesn't do what i want it to do. If you don't enter a password. You can still view the sheet, and definitely don't want that. Also it doesn't hide the sheet if the password is wrong.

 

[Michael M]

Hi
I did suggest that you use a Workbook_Open event first to hide the sheet
AND THEN use the code