Password protect single worksheet
Posted by Spencer on October 16, 2001 9:06 AM
Desired Scenario:
1. User opens spreadsheet.
2. Spreadsheet has four tabs.
3. User selects to view fourth tab (P&L).
4. Before able to view, user is prompted for a password.
5. If user enters correct password (airline), then allowed to view worksheet, else error message "incorrect password".
6. This password check happens each time the file is opened, but if the password is entered correctly once, then the password is not checked during the rest of file session.
Thanks in Advance!
Posted by Juan Pablo on October 16, 2001 9:13 AM
Try this one.
Public PvSh As String
Public Pwd As String
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Pwd = "" Then
If Sh.Name = "Sheet2" Then
Num = ActiveWindow.Index
Windows(Num).Visible = False
If Application.InputBox("Enter Password", "Password") <> "airplane" Then
MsgBox "Incorrect Password", vbCritical, "Error"
Application.EnableEvents = False
Sheets(PvSh).Select
Application.EnableEvents = True
Else
Pwd = "airplane"
End If
Windows(Num).Visible = True
End If
End If
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
PvSh = Sh.Name
End Sub
Juan Pablo
Posted by Spencer on October 16, 2001 9:22 AM
Thanks, however it didn't seem to work. I'm sure that I've skipped something obvious. Here's what I did with the code:
1. Open VB Editor.
2. Add Module.
3. Paste code.
Is there anything that I have to do before of after those three steps?
Thanks in advance!
Posted by Juan Pablo on October 16, 2001 9:25 AM
You have to copy the code into the Workbook module (Not into a new module)
Juan Pablo
Posted by Spencer on October 16, 2001 9:30 AM
From excel, not VB Editor, how do I add a module to the workbook?
Posted by Juan Pablo on October 16, 2001 9:34 AM
Ok, here it is
Sorry, i should be more specific. Copy the code i gave you. Now, go to excel, and in any sheet right click in the little Excel icon file and choose "View code".
That should take you to the VB Editor.
Delete the text that appears (If you had nothing there)
Private Sub Workbook_Open()
End Sub
Now, copy the text i gave you.
Juan Pablo
Posted by Spencer on October 16, 2001 9:42 AM
Re: Ok, here it is
Did that, nothing happens though. Do I have to run the macro first? or do i just save? I've tried to do both, and it's still not working. I don't know if it matters, but I'm using Excel 2000. Have you been able to get the code to work in a sample spreadsheet?
Posted by Juan Pablo on October 16, 2001 9:52 AM
Re: Ok, here it is
Yes i did, and it works for me.
Did you copy from
Public PvSh As String
to the second End Sub ?
Posted by Spencer on October 16, 2001 10:04 AM
Re: Ok, here it is
No, I only copy pasted exactly as printed into the VB Editor. If you don't mind, could you send the example to sqamanager@hotmail.com? I'd like to take a look at it to see what I'm still not getting right.
Posted by Spencer on October 16, 2001 10:42 AM
Re: Ok, here it is
No problem if you can't send the example.
Do I have to run the macro before it'll work, or do I just paste and save.
Posted by Juan Pablo on October 16, 2001 10:57 AM
Re: Ok, here it is
I just sent you the workbook, and you just have to copy and paste, it should work right after that, don't have to save previously either...
Juan Pablo
