totally hide worksheets until password is entered

[austenr]

I have seen this somewhere before but I can't remember where. What I want to do is on opening the workbook, all you see for the worksheet is white space until the correct password is given. After that the data and gridlines appear on the worksheet. And also only give the user three tries to open the workbook with the correct password and if not delete the workbook. Thanks

[matthewspatrick]

Unfortunately, you cannot do this without VBA. (Plain old workbook 'password to open' only gives one crack at it.) Of course, the rub there is that users may have VBA disabled, depending on security settings!

Patrick

[austenr]

What is the VBA way?

[matthewspatrick]

This sample workbook illustrates a common technique you might use instead...

Patrick

[matthewspatrick]

The password is pookie, BTW.

Also, you would need to lock the VBA project with a password to keep out prying eyes. That "security" can be hacked in minutes, though...

Patrick

[austenr]

That would work with a slight modification. Is there a way to detect active cells and have the shape cover them all instead of the way it is now? Thanks

[matthewspatrick]

I do not recommend that. In my opinion, this is handled better if there is just a dummy worksheet that tells the user s/he flunked the password test.

Patrick

[mvidas]

That sheet is also useful in case the user disables macros, you can put instructions there on how to change the security settings.

[matthewspatrick]

Matt,

I actually have two dummy worksheets in the example: one for 'enable your macros, bonehead!' and another for 'no password? No soup for you!'

Patrick

[XLGibbs]

The easiest way is to have a before_close to hide all relevant sheets and protect the file, and an on open event to undo those actions. Since the user must enable macros to see anything, it will force them to get the hint. I assume the presented method was similar.....

I have done it where on close the file hides everything except a sheet where only 1 cell is visible, and that sheet is protected. The cells contents are simply "Close file, re-open and select macros.

That sheet is actually hidden on_open, and all the visibility toggles opposite on close. All sheets are "protected" by Excel's crack security passwords though. LOL

I don;t have my sample of this handy, but if interested I will get one attached here if you are interested in alternate methods.

EDIT: Patrick, sorry didn't realize this was the method in your File...

[matthewspatrick]

Gibbs,

The method you described is the one I used in the example file posted above.

Cheers,

Patrick

[Justinlabenne]

One method I used for a while was to have the workbook where users need to enter a password locked down as tight as possible. Sheet + Workbook + VBA Project protection as well as a Macros Disabled Sheet, and all the other sheets xlVeryHidden.

The workbook cannot be opened unless of course you know the workbook password, and then have the ability to also unhide VeryHidden Sheets, among other things, but instead the users would have to open an Excel file with a password form, <password is entered correctly > the form's procedure opens and executes a procedure inside the locked down workbook that unlocks and unhides everything, and then shut's down the password workbook.>

This method is really only more of a slight deterent of the pre-mentioned methods, and can be bypassed and broken just like all others plus is a bit more extensive to set up. The attached example is something I was working on, but never really finished, all passwords are "123"

[austenr]

Justin,

I like this approach but I have a few modification questions.

1. How can I get the 123 out of the upper left cell on the Master key workbook?
2. How do I set the password as I would want to change it often?

Thanks. That is what I was looking for.

[mdmackillop]

Hi Austen,
You could save your password in the registry
http://vbaexpress.com/kb/getarticle.php?kb_id=208
Regards
Malcolm

[austenr]

First thanks to Justin for providing me the code earlier in this post. I want to modify it to do the following:


Add a button called ?Password Reset?. I would like to have my users be able to click the button and enter the current password and change it to what ever they desire.
The ?Show Password? button is currently linked to a specific workbook. I want to be able to add a way to browse for the workbook you want to have the password linked to. By entering the correct password, the sub should auto open the workbook being protected by the workbook that requests the password.
Thanks for any help.

[austenr]

XLGibbs,

Could you post your solution that you elude to above? Thanks

[XLGibbs]

Here you go. Basic sample. Sorry, thought this one was solved....let me know what you want to accomplish.

The workbook requestng password protected workbook providing password was a little tongue twisty and my brain hurt a little when reading it http://vbaexpress.com/forum/images/smilies/024.gif

[austenr]

Gibbs..

What I was hoping to accomplish was:

When you open the workbook, have all worksheets completely hiden until the correct password is entered. Also a way to easily change the password. Perhaps two buttons. One where you enter the current password and all sheets go from very hidden to visible. Another button that lets you enter the current password and then type in a new password. Kind of like most systems where you enter the old PW and then enter the new PW. Thanks

[XLGibbs]

Seems like overkill to me, especially in excel...I can't get ya something tonight, but will shoot you exactly what you asked for at some point tomorrow (it is 930PM here)...

Catch ya tomorrow.

[Justinlabenne]

I spent a minute on this here, the code is messy and incomplete, but I threw a bunch of stuff in the files for you to play around with and maybe make some sense of putting how you want it. The Databook file is Workbook protected with no password right now and the vba project is locked with a password of my first name "Justin".

The Key1 book will display one of two forms on AutoOpen, a form to set the databook password initially (you have to browse for the databook) and then open it, or the form to enter the password to open the Databook.xls file

The form to set the pwd create a ini file to store some cheaply encrypted info that the databooks uses. To change the password on Databook, delete the file and the Set form will come up again. Leave the file in place, and the form to just enter the pwd and open the file comes up.

This is not a complete solution by any means, and needs a lot of work to be finished IMO but it's a start. One thing, to reset the pwd, I just hack off the old one form databook, on seriously long passwords, that may not work as intended.

Enjoy..........

[coliervile]

Justin I tried to open your Key1 file and got an error.