Sleeper: VBA Controlled Passwords

[malik641]

I'm trying to create a macro that will be in full control of a worksheet's password (as well as the workbook's password).
For example:
Say you create a macro that protects a sheet upon opening a workbook. This seems helpful (and more automatic) knowing that the user doesn't have to re-protect the sheet after he/she make any necessary changes. But what happens if the user changes the password after they unprotect the sheet and saves the workbook with that new password? The next time they open the workbook there will be an error running the macro to a protected sheet (if the macro tries to perform disabled tasks with a protected sheet, anyway).

I want to disable the "Protect Sheet" completely and use only VBA to control this feature upon opening and/or closing.

Any ideas on how to approach this?

[TazGuy37]

I'm trying to create a macro that will be in full control of a worksheet's password (as well as the workbook's password).
...
But what happens if the user changes the password after they unprotect the sheet and saves the workbook with that new password? The next time they open the workbook there will be an error running the macro to a protected sheet (if the macro tries to perform disabled tasks with a protected sheet, anyway).


You want the users to have access to that password? What's the point of having one at all, then? Or...do you mean that a user could just open the VBE and see the password?

[Jacob Hilderbrand]

You could make a macro that the user could run to protect and unprotect the worksheet/workbook. Then just disable the protection option (which should be sufficient to stop most users from changing the password.



Application.CommandBars("Worksheet Menu Bar").Controls("&Tools").Controls("Protection").Enabled = False

[malik641]

You want the users to have access to that password? What's the point of having one at all, then? Or...do you mean that a user could just open the VBE and see the password?There is a password set to the workbook's worksheets everytime the workbook is opened (just incase anybody forgot to protect the sheet after it was changed) so users that shouldn't have access to certain items won't have access to them. But, if someone has the password to make any changes and reprotected the sheet with a Different password, then the macro fails upon opening the workbook and there is a password that only ONE person would know (if they told no one else about it). The password should ONLY be changed in the VBE.

DRJ hit the nail on the head, but I have another question. If someone has a custom toolbar with a "Protect sheet" button on it, it is not disabled by the code you gave me. How could I disable that as well?

[malik641]

Also, I want the user to be able to access the "Unprotect Sheet". Just not "Protect Sheet". And can the disable be workbook specific?

[Justinlabenne]

Sounds like this option would best suit your needs. Either have the sheets protected, and the certain cells that need updated "unlocked". This way the sheets and workbook do not have to be unprotected. Also, if you need code to run on the protected sheets, you should set the UserInterface property to True when applying protection.

Example:

Sheet1.Protect "password", , , , True

[malik641]

Sounds like this option would best suit your needs. Either have the sheets protected, and the certain cells that need updated "unlocked". This way the sheets and workbook do not have to be unprotected. Also, if you need code to run on the protected sheets, you should set the UserInterface property to True when applying protection.

Example:

Sheet1.Protect "password", , , , TrueThat's actually what I have now. So it's not exactly what I'm looking for, but thanks for the comment.

My MAIN goal was to use VBA to protect the sheets, with whatever password I assign to them. Enable the "Unprotect Sheet..." button, but DISABLE the "Protect Sheet..." button AFTER the sheet has been unprotected.

But after thinking about this for a while, I don't think I would run into the problem I stated in my first post.