Solved

Using "Protect" Workbook Worksheets across a Workbook

Posted on 2013-01-29
8
286 Views
Last Modified: 2013-01-30
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
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 300 total points
ID: 38830817
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38830942
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
 

Author Comment

by:Bright01
ID: 38831057
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
Independent Software Vendors: 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!

 
LVL 26

Expert Comment

by:redmondb
ID: 38831117
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
 

Author Comment

by:Bright01
ID: 38837071
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
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 200 total points
ID: 38837201
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
 

Author Closing Comment

by:Bright01
ID: 38837335
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38837386
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Increment default InPutBox value 14 26
Excel 2016 lost MRU list 8 67
Shade a cell based on date range 3 9
Excel format formula with percentage 3 6
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question