Password Protect viewing of individual worksheet in Excel 2010

[PThielan]

Hello all,

I am very new to this, so please be patient.

I have a very large spreadsheet in Excel 2010, and the creator would like every worksheet to be opened after entering a password. I've tried the protection available on the tab of the worksheet, but the creator does not even want people to view the data. So...in effect, the worksheet cannot be viewed in the spreadsheet until a password is entered.

To make it worse for me, I am not used to Excel 2010. I do have a VB script that I believe will work, but cannot get it to work in the workbook. I can view it, edit it, but not invoke it!!!
The script is:

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
End Sub

so...again to summarize:

1. User opens spreadsheet.
2. Spreadsheet has four tabs.
3. User selects to view second tab.
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 sheet is opened.

I would so appreciate some help!!!

Patricia

 

[SkipVought]

hi,

If a sheet is not to be viewed, then it must be HIDDEN (right-click sheet tab and select HIDE)

You will supply a list of sheet for the user to select, When the user supplies a password to your procedure fir the selected sheet, then UNHIDE the sheet.

Sheet Protection refer only to selecting and changing stuff on a sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PThielan]

Hi Skip,

I think your solution is the most simple! Here I was trying to figure out some fancy way to do it. Thanks so much for your help.

Patricia

 

[xlbo]

If all worksheets are to be able to be viewed after a password is provided why not simply put a password for opening on the workbook itself? If password is provided, workbook is opened and sheets are visible. If no passowrd provided, workbook will not open...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question