Running Macro’s on Password Protected Sheets (97)

Topic

New Reply

I have a rather complex sheet which is accessed by several different users. Password protecting the sheet is a must to protect the structure. However, I also have 3 different command/macro buttons on the form, that set different parameters based on what type of data is being entered. The problem is when the sheet is “password” protected, the macros will stop running, and I get an error message. How can I “code” past the protection, either through un-locking, running the macros, and re-locking, or in some other similar method.

Reply | Quote

Replies

just disable the protection, before you actually execute your code, then enable it again

e.g.
Sub MyMacro()
ActiveSheet.Unprotect password:=”MyPassword”
‘put your code here
ActiveSheet.Protect password:=”MyPassword”
End Sub

Reply | Quote

Great. Thanks, it works fine. Second question then. Is it possible to use code to disable the Macro functions under the “Tools” menu, so that the user cannot open the Visual Basic Editor to see the password in the code. Naturally it would be for only this heavily used sheet only.

Reply | Quote

No, the user would simply press alt-F11 and access your code.

You will have to lock your project from view. In the VBE: Tools, VBAProject properties, Protection.

Reply | Quote

Unprotect the sheet, do your stuff, then protect it again, like this:

    Worksheets("MyProtectedSheet").Unprotect ("MyPassword")
    'Do your stuff here
    Worksheets("MyProtectedSheet").Protect ("MyPassword")

Reply | Quote