Password protect viewing of single worksheet

Posted by Spencer on October 12, 2001 10:31 AM

1. Should see the worksheet tab
2. When click on single worksheet tab, prompt for password
3. Successful entry of password allows user to view and edit data in worksheet.

I know how to protect the viewing of an entire workbook, as well as the protection of editing, however I am looking for a password-protect solution that only protects the viewing of a single worksheet.

Thanks in Advance!

Posted by David Kelly on October 12, 2001 5:31 PM

Why not set the tex colour to white so as the spreadshhet can't be seen.

set up a button on the sheet and assign a macro that unprotects the sheet and sets the colour back to automatic. (easy to record)

Protect the sheet with a password

when the user presses the button it will prompt for a password before unprotecting the sheet and setting the colour back to automatic (visible)

Posted by David Kelly on October 12, 2001 5:59 PM

Ps you will have to select all cells and format them as hidden (format cells, protection, hidden)priot to protecting the sheet as then unhide them in the macro otherwise they will be seen in the formula bar

Posted by Juan Pablo on October 16, 2001 5:47 AM

Try this, i think it's does what you're asking. Copy it into your workbook module. Change "Sheet2" to the worksheet you're trying to protect.

Public PvSh As String

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = "Sheet2" Then
Num = ActiveWindow.Index
Windows(Num).Visible = False
If InputBox("Enter Password", "Password") &LT;&GT; "Mypassword" Then
Sheets(PvSh).Select
End If
Windows(Num).Visible = True
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:01 AM

Problem

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!