Solved

Using "Protect" Workbook Worksheets across a Workbook

Posted on 2013-01-29
8
277 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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.

789 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