Solved

Using "Protect" Workbook Worksheets across a Workbook

Posted on 2013-01-29
8
271 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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