Macro to protect multiple sheets?

[Stilla]

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
__________________________

 

[Duke Carey]

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

 

[Stilla]

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.

 

[Duke Carey]

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

 

[xrbbaker]

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

 

[xrbbaker]

Never mind. I found my error. Sorry to bother. Thanks for the help.

Russ

 

[Stilla]

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
_____________

 

[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

 

[Stilla]

Wonderful! Thanks Gord!

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

 

[Stilla]

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!

 

[Dave Peterson]

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

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

 

[Stilla]

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?

 

[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.