Simple Username Password Help

You have a couple options. The one that most closely matches what you're asking is to add code to the Workbook_Open procedure. That code would check the user name against a list stored in a secure place in the workbook, and hide/unhide sheets as appropriate.

The downside is 1)you must have macros enabled for it to work. If a user opens the workbook without macros, you need a default page to come up telling them they need to enable macros. All other sheets will be completely hidden from them. 2) When the workbook is closed, you must revert it to its original form, meaning hiding all the confidential sheets. That's not difficult, but you do run the small chance of some weird power failure occuring and the workbook_close procedure not firing, AND the user has saved the workbook during their session.

Another option is to build a user form in Excel where you login with a password, then depending on your permissions you can view certain data. If you output the data to a worksheet, you run the same small risk of the data being saved in the open with the workbook. You could just present all the information inside the userform and that would prevent that.

Finally, probably the most secure way, is one workbook per person, with a password to open the workbook. Then your managers have their own summary workbooks that add up all the other ones.

Does that help? Indicate which route you're interested in and we can look at some code.

I would be interested in the same thing with a User Form. How would I get it to function without enabling macros? If somebody disabled macros wouldn't the password be turned off?

I would be interested in the same thing with a User Form. How would I get it to function without enabling macros? If somebody disabled macros wouldn't the password be turned off?

Click to expand...

I think the idea would be to have the workbook saved with all sheets set to VeryHidden... then the appropriate sheets get unhidden according to the code behind the Userform. Furthermore, the project would ALSO be Password Protected, so the ONLY avenue into the sheets would be using a password either A)through the userForm or B)through the project explorer. In this way, they must enable macros in order to execute the code for the userform, otherwise, they can't see anything.

Yep, sorry to mislead you, you have to use macros with user forms. All your data sheets would be VeryHidden.

The plus side of using userforms, AND displaying your data within the userform, is you never have to worry about a worksheet left unintentionally exposed.

I don't think you will ever get the kind of security you need. You'll never be able to protect your code or any passwords. But here is something that I was working on that might get you started.

You need a sheet that has all your access privilidges listed for each sheet. I called mine "Access" and here is an example
You also need a sheet that everyone can access, I called mine "Public". If someone tries to access a sheet that they don't have the privilidge to, the Public sheet will be selected instead.

Here is the code that goes in ThisWorkbook

Milo, I agree that the level of security that you have presented is pretty low. And that may be acceptable for your application. However, the method that Chris is describing is considerably more secure.

In your example, all sheet tabs are visible, so the user is aware of all sheets that are contained in the workbook. Chris is recommending setting the Visible attribute of each sheet to VeryHidden in the Project explorer... the user can't even get a listing of these sheets if they go to Format->Sheet->Unhide... in fact, I believe that the option will be grayed out.

In your example, it is possible to view all worksheets if the user disables macros, but if sheets are VeryHidden, they can only be made visible through code, or through the VBE. If the Project is Password protected, you can't get there through the VBE, and if the user turns off the macros, then he can't get there through code.

Can a user crack the security in Chris's example? Absolutely, but they have to be pretty savvy, and they have to WANT to do it... there is no casual way to OOPS your way where you don't belong, as there is in your example.

Good tips. I used the same ideas as in my prior post but modified the code to include xlveryhidden.

What about something like this

That is just about as good as you can get with this method... Password protecting the Project (which you may very well have implemented, but we just can't see it from the Code) is another layer of proection, to prevent a user from simply using the VBE to change the .Visible properties.

The only real ***** in this method at that point, is the following scenario: an Admin user saves the workbook while certain sheets are visible, followed by a crash. Then somebody else opens the file with Macros disabled, and they can see what the Admin saw. But that's a pretty sketchy series of events... and usually someone re-opens the file after a crash, at which point the security should get re-set. One way to fix that, as Chris recommends, is to display the data in a userform, rather than unhiding sheets. Another way to fix it is this: Move the code for Open and Close to 2 subs in a Standard Module. In the Open and Close events, call the appropriate Subs... but also on the Save event, trigger the Close Sub (and save the ActiveSheet name) before saving, and trigger the Open Sub after saving (don't forget to activate the sheet that was active before the Save)... But it may not be worth the time to fix it, either.

One other thing I'd like to point out is if the User has WithEvents set to false then the Open event never gets raised. This can easily be fixed by moving the code to a Standard module in a Sub called Autpen. The only way to disable THAT is to disable macros. To be honest, i have never seen a user disable events... but everything I have read warns pretty strongly about it... take it as you will.

I like how the title of this thread is "Simple Username Password Help". Yea, real simple.


I'm not sure how to use forms to accomplish the OPs goal, but my 2nd solution can be defeated by running this code (which could be entered in a different workbook) while in the workbook.


You would need an additional level of security at the Sheet_activate level to further prevent access to sheets