Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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.
0
Bright01
Asked:
Bright01
  • 4
  • 3
2 Solutions
 
Patrick MatthewsCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
redmondbCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now