password protected

1. Ok, press ALT-F11 to open the VB editor on your workbook.
2. Double click on ThisWorkbook in the left panel
3. Paste ALL the following code into the window
   
   

   Private Sub Workbook_Open()
   Dim i_pwd As String
   
   i_pwd = InputBox("Please Enter Password", "Password Required...")
   If i_pwd = "" Then
   Exit Sub
   End If
   
   Select Case LCase(i_pwd)
       Case Is = "staff"
       Worksheets("Sheet1").Visible = True
       Sheets("Sheet1").Select
   
       Case Is = "admin"
       UnhideSheets
       Sheets("Sheet1").Select
   
   Case Else
   MsgBox "Incorrect password; no action taken.", vbInformation, "Wrong Password!"
   End Select
   
   Exit Sub
   End Sub
   Private Sub Workbook_BeforeClose(Cancel As Boolean)
       HideSheets
   End Sub
   Private Sub HideSheets()
   Dim sht As Object
   
   Application.ScreenUpdating = False
   
   ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible
   
   For Each sht In ThisWorkbook.Sheets
   
   If sht.Name <> "Sheet1" Then sht.Visible = xlSheetVeryHidden
   
   Next sht
   
   Application.ScreenUpdating = True
   
   ThisWorkbook.Save
   
   End Sub
   Private Sub UnhideSheets()
   Dim sht As Object
   
   Application.ScreenUpdating = False
   
   For Each sht In ThisWorkbook.Sheets
   
   sht.Visible = xlSheetVisible
   
   Next sht
   
   Application.ScreenUpdating = True
   
   End Sub

4. Edit the code everywhere you see Sheet1 to the name of your first sheet
5. Edit the password to replace "admin" with your real admin password
6. Right-click on ThisWorkbook and select VBAProject Properties
7. On the Protection tab, click the box to Lock project for viewing and enter the "admin" password. (this hides the code so your staff can't edit it or see the passwords therein)
8. Click OK
9. Press Ctrl-Q to close the editor
10. Save and close your workbook

Now, when the book is closed, all sheets are automatically hidden except the sheet you listed as "Sheet1". When you open the sheet it prompts for the passwords you mentioned. If "admin" is given, all sheets are unhidden.

Attached is a sample.

If this takes care of your need, be sure to EDIT your original post and set the PREFIX box as [SOLVED]

You also need code in the BeforeSave event to hide sheets, save, and then restore visibility to approriate sheets.

Otherwise, if an admin saves the workbook (all sheets visible) and aborts Excel (no BeforeClose event processing), the next user can open the workbook with macros disabled and see everything.

Originally Posted by shg

You also need code in the BeforeSave event to hide sheets, save, and then restore visibility to approriate sheets.

Otherwise, if an admin saves the workbook (all sheets visible) and aborts Excel (no BeforeClose event processing), the next user can open the workbook with macros disabled and see everything.

Ouch, you are right!

I designed this to be added to what you have already:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    HideSheets
    ThisWorkbook.Save

Dim i_pwd As String

i_pwd = InputBox("Document Saved - Please Enter Password", "Password Required...")
If i_pwd = "" Then
Exit Sub
End If

Select Case LCase(i_pwd)
    Case Is = "staff"
    Worksheets("Sheet1").Visible = True
    Sheets("Sheet1").Select

    Case Is = "admin"
    UnhideSheets
    Sheets("Sheet1").Select

Case Else
MsgBox "Incorrect password; document was saved", vbInformation, "Wrong Password!"
End Select
End Sub

It works, but there's a problem. It's asking for you to re-enter your password, which i wanted, but it's asking twice, and I don't know why.

SHG, can you spot the error? Or, do you know how we can just memorize the state of the current sheets first, then run HideSheets, save, then restore the state of the previous sheets "visibility"?

I'd have some array variant 'constants' that are set in the Open event for each password and contain the visible worksheets for that password. When a password is entered, the 'current user' array variant gets the appropriate list.

When the workbook is saved, all sheets are hidden, and then the 'current user' array is restored to visibility.

Your post does not comply with Rule 3 of our Forum Rules. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here http://www.excelforum.com/misc.php?do=bbcode#code

I missed the detail you mentioned you wanted Sheet1 to be read-only by staff. I fixed that code and solved the multi-password prompts.

Replace ALL the old code with this:

Private Sub Workbook_Open()
Dim i_pwd As String

i_pwd = InputBox("Please Enter Password", "Password Required...")
If i_pwd = "" Then
Exit Sub
End If

Select Case LCase(i_pwd)
    Case Is = "staff"
    Worksheets("Sheet1").Visible = True
    Sheets("Sheet1").Select

    Case Is = "admin"
    UnhideSheets
    Sheets("Sheet1").Select

Case Else
MsgBox "Incorrect password; normal entry only.", vbInformation, "Wrong Password!"
End Select
Exit Sub
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    HideSheets
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    X = False
    Sheets("Sheet1").Select
    If ActiveSheet.ProtectContents Then X = True
    If ActiveSheet.ProtectDrawingObjects Then X = True
    If ActiveSheet.ProtectScenarios Then X = True

    If X = False Then
        HideSheets
        ThisWorkbook.Save
        UnhideSheets
    Else
        ThisWorkbook.Save
    End If
End Sub
Private Sub HideSheets()
Dim sht As Object

Application.ScreenUpdating = False
     
    Sheets(1).Protect Password:="admin", UserInterfaceOnly:=True
    
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible

For Each sht In ThisWorkbook.Sheets

If sht.Name <> "Sheet1" Then sht.Visible = xlSheetVeryHidden

Next sht

Application.ScreenUpdating = True

End Sub
Private Sub UnhideSheets()
Dim sht As Object

Application.ScreenUpdating = False

For Each sht In ThisWorkbook.Sheets

sht.Visible = xlSheetVisible

Next sht
    Sheets(1).Unprotect Password:="admin"
    Application.ScreenUpdating = True

End Sub

I'm locking this post until the OP decides to read & follow the rules. Not only has he/she consistently needed reminding to use Code Tags this post has also been duplicated:

http://www.excelforum.com/excel-prog...gin-error.html