Macro to protect multiple sheets?

G

Guest

Hello! Thanks in advance for your help.

How can I modify the macro (below line) I recorded to protect a sheet so
that I can use it multiple sheets at once. I have a workbook with 20 tabs.
I'd like to be able to protect and unprotect all sheets at once.
_________________________
PROTECTSHEET Macro
Keyboard Shortcut: Ctrl+p

ActiveSheet.Protect Password:="mypassword", DrawingObjects:=True,
Contents:=True, Scenarios:=True
__________________________
 
G

Guest

Dim ws as worksheet
for each ws in thisworkbook.worksheets
ws.Protect Password:="mypassword", DrawingObjects:=True,
Contents:=True, Scenarios:=True
next ws
 
G

Guest

Duke - Fantastic! it WORKS!! One small glitch...

When I protect, I'm still able to select locked cells... how do I modify
this so that the user is only able to select unlocked cells on a protected
sheet?

Thanks again.
 
G

Guest

try
Dim ws as worksheet
for each ws in thisworkbook.worksheets
ws.Protect Password:="mypassword", DrawingObjects:=True,
Contents:=True, Scenarios:=True
ws.EnableSelection = xlUnlockedCells
next ws
 
G

Guest

Duke,

Pardon my jump in, but I have the exact same need. I tried your solution
but it seems only to work on the first worksheet. Any idea why that may be?

Thanks - Russ

Sub protectit()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

ActiveSheet.Protect Password:="myPW", DrawingObjects:=True,
Contents:=True, Scenarios:=True
Next ws

End Sub
 
G

Guest

OH HOW VERY VERY COOOOL! Thanks Duke! You have already saved me a
significant amount of time!!

I adjusted slightly to now work on active workbooks(For Each ws In
ActiveWorkbook.Worksheets)

Question 1) How could I refine this further to apply to only the selected
worksheets in the active workbook?

Question 2) My UN-protecting Macro (For Each ws In ActiveWorkbook.Worksheets
ws.UNPROTECT) works but it requires me to enter the password as it moves
from tab to tab. How can I modify so that I can unprotect ALL selected
worksheets with only ONE entry of the password?

THANKS SO MUCH - I'm starting to become very fond of Macros!

Stilla
_____________
 
G

Gord Dibben

Stilla

1. Use this code

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each WS In MySheets
WS.Select
WS.Protect Password:="justme"
Next WS
End Sub

2. The sheets have to first be protected with the same password for each.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
G

Guest

Wonderful! Thanks Gord!

Gord Dibben said:
Stilla

1. Use this code

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each WS In MySheets
WS.Select
WS.Protect Password:="justme"
Next WS
End Sub

2. The sheets have to first be protected with the same password for each.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
Sheets(n).Protect Password:="justme"
Next n
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP
 
G

Guest

Hello Duke!
Regarding the LAST line: "ws.EnableSelection = xlUnlockedCells" This works
great as long as the workbook remains open. HOWEVER, if I close it and open
it again, then I found that I can still select the locked cells (don't want
this). Do you have another piece of magic code to make it right?

THANKS SO MUCH!
 
D

Dave Peterson

Put the procedure in a general module and call the procedure Auto_Open.

Excel time you open the file, auto_open() will run (if you allow macros to run).
 
G

Guest

Hi Dave. Thanks for your response. Here's my situation:
THe macro I am using (for various reasons) cannot reside in the file that
I'm working on. I work on files that have multiple tabs, and would like a
way of protecting and unprotecting quickly. My current macros are in a macro
file...and thanks to the help I found here, are working fine, except for the
last issue that you responded to.

Will your solution work if the ultimate recipient of my work file is not
sent the macro?
 
D

Dave Peterson

Do you mean just the selection of unlocked cells?

I think that it'll depend on the version of excel your recipient uses.

I _think_ that xl2002+ has that fine grained option unders
tools|protection|protect sheet. (xl2003 remembers it. I don't recall if xl2002
does.)

But if you don't share the macro -- and have it run some way, then it won't
work.

Maybe you could have your macro work against the activeworkbook instead of
thisworkbook. It won't help the recipient, but it may be enough for you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top