Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

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!


Check out our Excel VBA Resources

Re: Password protect single worksheet

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


Re: Password protect single worksheet

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!


Re: Password protect single worksheet

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


Re: Password protect single worksheet

Posted by Spencer on October 16, 2001 9:30 AM
From excel, not VB Editor, how do I add a module to the workbook?



Ok, here it is

Posted by Juan Pablo on October 16, 2001 9:34 AM
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



Re: Ok, here it is

Posted by Spencer on October 16, 2001 9:42 AM
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?

Re: Ok, here it is

Posted by Juan Pablo on October 16, 2001 9:52 AM
Yes i did, and it works for me.

Did you copy from

Public PvSh As String

to the second End Sub ?


Re: Ok, here it is

Posted by Spencer on October 16, 2001 10:04 AM
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.

Re: Ok, here it is

Posted by Spencer on October 16, 2001 10:42 AM
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.


Re: Ok, here it is

Posted by Juan Pablo on October 16, 2001 10:57 AM
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.