Using "Protect" Workbook Worksheets across a Workbook

EE Pros,

I have a fairly complex Workbook that has a number of different tabs and macros.  Whenever a Macro is "fired", I'd like to unlock the specific worksheet (active) and relock after it ends.  The problem I have right now is I'm using code to do that with each Macro.  It's getting to be unmanagable and I'd like to know if there is a way to have a Workbook Macro that would do the unlock/lock for each WS but only when a Macro is fired and I can control this in one single location within the code.  Also, is there a way to turn it off and on without actually going into the code?  In other words, have a macro (with a button) that activates the locking function (the universal macro described above).

Thank you in advance.

B.
Bright01Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
You could simply have subs to handle that, and call those subs from your main procedures:

Sub UnprotectActiveSheet()

    ActiveSheet.Unprotect "password" 'omit password if not used

End Sub

Sub ProtectActiveSheet()

    ActiveSheet.Protect "password" 'omit password if not used

End Sub

Open in new window

0
 
redmondbCommented:
Hi, Bright01.

As I understand it, you're trying to avoid having to include any password processing code in each of the macros. How are the macros called in the first place?

Having a button to Lock/Unlock the password processing is straightforward.

Regards,
Brian.
0
 
Bright01Author Commented:
Mathewspatrick,

Your recommendation is how I'm doing it now.  However, I have to put that code in every macro.

Redmondb,

"yes"; that is what I'm trying to avoid.  Macros are called many different ways.  They are called when the WB is initially launched (displays a note to the user), they are called when you select a particular item from a drop down, they are called when something changes to a particular cell in a WS and they are also attached to buttons for doing certain actions like showing a graphic or adding lines to a WS.  It's fairly complex with regard to the types of macros.  Right now I'm using the code that Mathewpatrick has recommended above, but having to put it into each macro.  I thought there might be a better way.

B.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
redmondbCommented:
Thanks, Bright01.

Yes, I can see the scale of your problem! I was going to ask about the possibility of using one of more Event macros, but frankly your file is already complicated enough without adding another level of complexity - the one-off drudgery of including a line of code at the start and end of each macro is probably preferable.

Regards,
Brian.
0
 
Bright01Author Commented:
Brian,

Here are some ideas to consider;

Could we create a macro that could be represented as one line in other macros (at the beginning) that would automatically unlock then relock a macro?

or

Can I reference a particular cell on a "administrative tab" that is the actual "password", so that when I change it in one spot, it reflects the change in all the macros that use it?

or

Can I have a macro that turns off all the protection regardless of any macro trying to turn protection on?

If any of these are possibilities that seem easy to accomplish, I can author different questions for each.........

B.
0
 
redmondbConnect With a Mentor Commented:
Bright01,

Some interesting idea and I've commented on each. However, nothing seems to be as effective or as simple as matthewspatrick's suggestion! It could even be applied by a macro which read all your code looking for SUB's, after each of which it added a Call to matthewspatrick's macro. (And similarly before each End Sub.)

Could we create a macro that could be represented as one line in other macros (at the beginning) that would automatically unlock then relock a macro?
 - a timer. (Dangerous.)
 - if every one of the macro caused something to change as they finished then you might be able to use an Event macro.
- You're willing to manually add the one line at the beginning, so why not the one at the end, which is even easier to add!

Can I reference a particular cell on a "administrative tab" that is the actual "password", so that when I change it in one spot, it reflects the change in all the macros that use it?
 - Absolutely - password is simply a string, it doesn't matter where it comes from. (Except that it's marginally safer in your code than anywhere else.)

Can I have a macro that turns off all the protection regardless of any macro trying to turn protection on?
 - I don't see how you could do this, unless you could tie the running of all the other macros into an Event.

Regards,
Brian.
0
 
Bright01Author Commented:
Disappointed.....some things are just not meant to be...... it's life!  Thank you for brainstorming with me; I'll continue to do it with each macro.

Thanks again,

B.
0
 
redmondbCommented:
Thanks, Bright01.

(I'm still not sure what you had against the "open" and "close" macros, if you'd care to explain...?)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.