How do I protect several worksheets at the same time?
 

I have several worksheets that have similar formats and information. One for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update the
sheets daily. Now I would like to turn on worksheet protection but it seems
I have to turn on protection one sheet at a time. Is there any way to turn
protection on and off for several or all sheets at once?

How do I protect several worksheets at the same time?
 

You can use a for each loop for all (except those NOT desired) or an array
of just those desired.


--
Don Guillett
SalesAid Software

"JohnH240" wrote in message
...
I have several worksheets that have similar formats and information. One
for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update
the
sheets daily. Now I would like to turn on worksheet protection but it
seems
I have to turn on protection one sheet at a time. Is there any way to turn
protection on and off for several or all sheets at once?

How do I protect several worksheets at the same time?
 

You can do it with a macro. This one assumes all sheets have the same password.

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
sht.Protect Password:="mypassword"
Next sht
End Sub

Mike

"JohnH240" wrote:

I have several worksheets that have similar formats and information. One for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update the
sheets daily. Now I would like to turn on worksheet protection but it seems
I have to turn on protection one sheet at a time. Is there any way to turn
protection on and off for several or all sheets at once?

How do I protect several worksheets at the same time?
 

and you should be able to do it withOUT the selections

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets

if sht.name<"Main" then sht.Protect Password:="mypassword"

Next sht
End Sub


--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
You can do it with a macro. This one assumes all sheets have the same
password.

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
sht.Protect Password:="mypassword"
Next sht
End Sub

Mike

"JohnH240" wrote:

I have several worksheets that have similar formats and information. One
for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update
the
sheets daily. Now I would like to turn on worksheet protection but it
seems
I have to turn on protection one sheet at a time. Is there any way to
turn
protection on and off for several or all sheets at once?

How do I protect several worksheets at the same time?
 

Thank you both. I don't know how to do these things yet, but like everything
else in MS Office, expecially Excel, I guess I will dig into the help files
and figure it out, or browse through the training on MSN. Seems like I've had
to keep learning something new since the late 70's and my first round with
punch cards!
Thanks again.
Anyone want to give a short primer on where to start with macros? If not, no
problem.

"Don Guillett" wrote:

and you should be able to do it withOUT the selections

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets

if sht.name<"Main" then sht.Protect Password:="mypassword"

Next sht
End Sub


--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
You can do it with a macro. This one assumes all sheets have the same
password.

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
sht.Protect Password:="mypassword"
Next sht
End Sub

Mike

"JohnH240" wrote:

I have several worksheets that have similar formats and information. One
for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update
the
sheets daily. Now I would like to turn on worksheet protection but it
seems
I have to turn on protection one sheet at a time. Is there any way to
turn
protection on and off for several or all sheets at once?

How do I protect several worksheets at the same time?
 

John

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run or edit the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord Dibben MS Excel MVP


On Thu, 1 Mar 2007 07:28:10 -0800, JohnH240
wrote:

Thank you both. I don't know how to do these things yet, but like everything
else in MS Office, expecially Excel, I guess I will dig into the help files
and figure it out, or browse through the training on MSN. Seems like I've had
to keep learning something new since the late 70's and my first round with
punch cards!
Thanks again.
Anyone want to give a short primer on where to start with macros? If not, no
problem.

"Don Guillett" wrote:

and you should be able to do it withOUT the selections

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets

if sht.name<"Main" then sht.Protect Password:="mypassword"

Next sht
End Sub


--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
You can do it with a macro. This one assumes all sheets have the same
password.

Sub MsgBoxAllMySheets()
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
sht.Protect Password:="mypassword"
Next sht
End Sub

Mike

"JohnH240" wrote:

I have several worksheets that have similar formats and information. One
for
each day of the month. I have locked the same groups of cells in each
worksheet and left certain groups of cells unlocked so users can update
the
sheets daily. Now I would like to turn on worksheet protection but it
seems
I have to turn on protection one sheet at a time. Is there any way to
turn
protection on and off for several or all sheets at once?