User name and password to unhide worksheets during Excel file is opened

Hi, this should do what you want.
Usernames & passwords are stored on sheet4

Hi Sean Thomas,
It works very good. There is slight problem, when I save the, the sheet 2-4 would be closed because
once a while I click "save" to save my work and continue to work on. Is there a way to hide the sheets,
when the file is closed? If the file has not been saved, click "closed" will ask to save a file, then the sheet2
will be hidden and save, then close the file.

Is that possibe or better to save the file without hiding the sheets, even when the file is closed. When the file
is reopened, then vba will hide the sheet2, 3 and 4; then start at sheet1 to enter user anme and password?
I like this approach, what do you think? I hope that is possible.

Thanks of your help!

Hi, if you save the file without hiding the sheets, when you open the file again the other sheets will be visible until macros are enabled. So if the user doesn't enable macros they will be able to access the other sheets. Then there is no point having a password.

Take a look at the attached. Passwords are case sensitive and accept punctuation.
There are four users:
admin (password: "admin" (no quotes))
Bob (password: "Bob's Password")
Mary (password: "Mary's Password")
Dave (password: "Dave's Password")

putting the admin username and password will show all the sheets, including HiddenSheet, which has the username/password/permissions data.
Note that Mary and Dave have a permitted sheet in common.

Put this code in a normal module.

Please Login or Register  to view this content.

This in the code module for Sheet1 (the one with the username/password verification)

Please Login or Register  to view this content.

and this in the ThisWorkbook code module

Please Login or Register  to view this content.

At the end of sub TestPasswordMatch, change this line of code

Please Login or Register  to view this content.

The question of Bob getting access to a sheet that he adds can be addressed by requiring that there be some "admin". The Workbook_SheetActivate would check if admin has permission to access sh.Name. If so, do nothing. If admin does not have access, that indicates that sh is a new sheet and sh.Name should be added to both admin's permissions and the permissions of the current user. That describes how to code it, but I don't have time to do the coding right now. The other question is "what to do if an un-verified user adds a sheet while looking at the log-in sheet"?

Hi Rocky,
sorry its not very clear what you are asking?

Hi Rocky,
It is set up to hide sheets when saving/closing. Reason for this is if you save the file with the sheets open and then carry on working but then decide to close the file without saving changes, it will save the file with the sheets open and therefore when you open the file again the sheets will be visible. Thus rendering the whole exercise useless.

There might be a way around it, but it would involve saving the latest one as a temporary file, and then opening the original saved file and saving with sheets closed then close the temporary file.
This will need a bit of playing around with of which im afraid I don't have the time for now.

Here's a version which accommodates the user adding sheets.
It does not accommodate the user changing sheet names. Its a question of convenience.
As is, the hidden permissions sheet is based on tab names. It could be switched to using code names, but the admin would have to realize that and act appropriately when giving permission to users for specific sheets.
A code name based permissions sheet will be proof against the user changing sheet names.

Hi Rocky,
I have changed the code so that it does the following.
1.need password to open other sheets.
2.If you save changes when closing it will close other sheets and save then close.
3.if you don't make any changes it will just close.
4.if you save workbook with other sheets open, then close it closes other sheets, saves and closes.
5.if you save workbook with other sheets open, make changes but choose to close without saving changes, it saves current file as temp file, opens the last saved file, closes other sheets, saves & closes original file, and then closes temp file.

This should do what you want.
Code to open worksheets is in sheet1
code to close worksheets is in thisworkbook and module 1