password protected

[lyealain]

hi may i know how do i protect my excel in this scenario below:

when they click open excel, my excel sheet will require them for password..
if the passowrd is "staff", only the first tab can be viewed by all the staff.
Otherwise, if the password is "admin" , then all the tab can be viewed..

Please kindly advice me on the code..

[JBeaucaire]

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
   

   Please Login or Register  to view this content.

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]

[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.

[JBeaucaire]

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:

Please Login or Register  to view this content.

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"?

[shg]

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.

[lyealain]

Thanks for the wonderful code..
but i face the problem here..

it keep prompting me to enter password .. even when i want to close the excel.

also, even i enter the passwword wrongly, i am still able to edit the sheet 1 page.
when the login box prompt, i can edit the sheet1 by just close the login box..

Please advice

[lyealain]

Hi why the login don work well here.. how do i stop user from opening the the SaleOrder page if the password enter is not either "staff" or "admin".

i tried use the Exit Sub.. but is not working here..
if the password enter is wrong, shut the whole excel


Private Sub Workbook_Open()
Dim i_pwd As String
i_pwd = InputBox("Please Enter Password")

If i_pwd = "" Then
MsgBox "Incorrect password; no action taken.", vbInformation, "Wrong Password!"
Exit Sub
End If

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

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

Case Else
MsgBox "Incorrect password; no action taken.", vbInformation, "Wrong Password!"
Exit Sub
End Select
End Sub

[royUK]

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

[JBeaucaire]

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:

Please Login or Register  to view this content.

[royUK]

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