Macro to protect multiple sheets?
 

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
__________________________

Macro to protect multiple sheets?
 

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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








"Duke Carey" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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

Russ

"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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
_____________
"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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

On Wed, 28 Feb 2007 08:18:39 -0800, Stilla
wrote:

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
_____________
"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

Wonderful! Thanks Gord!

"Gord Dibben" wrote:

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

On Wed, 28 Feb 2007 08:18:39 -0800, Stilla
wrote:

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
_____________
"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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!

"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________

Macro to protect multiple sheets?
 

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

Stilla wrote:

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!

"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________


--

Dave Peterson

Macro to protect multiple sheets?
 

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" wrote:

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

Stilla wrote:

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!

"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________


--

Dave Peterson

Macro to protect multiple sheets?
 

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.



Stilla wrote:

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" wrote:

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

Stilla wrote:

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!

"Duke Carey" wrote:

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



"Stilla" wrote:

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" wrote:

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

"Stilla" wrote:

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
__________________________


--

Dave Peterson


--

Dave Peterson