I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It restricts
things I don't want to restrict.

Take your pick:-

Public Sub ToggleProtect1()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub



Sub Toggleprotect2()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub



Public Sub ProtectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub



Public Sub UnprotectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"CheriT63" wrote in message
...
I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It
restricts
things I don't want to restrict.

Only with a VBA macro:

Sub ProtectSheets()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Protect ' or .Unprotect
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"CheriT63" wrote in message
...
I find it tedious to have to unprotect each worksheet
individually when I
need to edit or update a spreadsheet. The same is true when I
want to
protect them again. Isn't there some way to do all worksheets
at the same
time?

Protecting the workbook as a whole is not a workable solution.
It restricts
things I don't want to restrict.

I appreciate your information. I am not that familiar with using Excel VBA.
How would I work these? And were there three different macros you listed? I
was unable to determine where one ended and the other started.

Thanks again

"Ken Wright" wrote:

Take your pick:-

Public Sub ToggleProtect1()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub



Sub Toggleprotect2()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub



Public Sub ProtectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub



Public Sub UnprotectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"CheriT63" wrote in message
...
I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It
restricts
things I don't want to restrict.

Hi Chip,

Thank you for your reply! I know how to get in to VB editor. Do I just
copy and paste this with minor changes (worksheet #s; unprotect or protect)?
How do I run it then?

Thanks

"Chip Pearson" wrote:

Only with a VBA macro:

Sub ProtectSheets()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Protect ' or .Unprotect
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"CheriT63" wrote in message
...
I find it tedious to have to unprotect each worksheet
individually when I
need to edit or update a spreadsheet. The same is true when I
want to
protect them again. Isn't there some way to do all worksheets
at the same
time?

Protecting the workbook as a whole is not a workable solution.
It restricts
things I don't want to restrict.

Cheri

Only through VBA.

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

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

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 the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP


On Sat, 8 Jan 2005 10:27:02 -0800, "CheriT63"
wrote:

I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It restricts
things I don't want to restrict.

Thank you so much for the thorough explanation and the step by step
directions!!! I will definitely check out the suggested website as well.

Thanks again!

"Gord Dibben" wrote:

Cheri

Only through VBA.

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

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

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 the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP


On Sat, 8 Jan 2005 10:27:02 -0800, "CheriT63"
wrote:

I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It restricts
things I don't want to restrict.

Oh my word! This is awesome!!! I love it...thank you so much!

"Gord Dibben" wrote:

Cheri

Only through VBA.

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

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

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 the macro by going to ToolMacroMacros.


Gord Dibben Excel MVP


On Sat, 8 Jan 2005 10:27:02 -0800, "CheriT63"
wrote:

I find it tedious to have to unprotect each worksheet individually when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the same
time?

Protecting the workbook as a whole is not a workable solution. It restricts
things I don't want to restrict.

Open the VBA Editor, and go to the Insert menu and choose Module
(not Class Module). Paste the code in to that module. Then you
can run the macro from the Macros dialog in Excel (ALT+F8).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"CheriT63" wrote in message
...
Hi Chip,

Thank you for your reply! I know how to get in to VB editor.
Do I just
copy and paste this with minor changes (worksheet #s; unprotect
or protect)?
How do I run it then?

Thanks

"Chip Pearson" wrote:

Only with a VBA macro:

Sub ProtectSheets()
Dim WS As Worksheet
For Each WS In Worksheets
WS.Protect ' or .Unprotect
Next WS
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"CheriT63" wrote in
message
...
I find it tedious to have to unprotect each worksheet
individually when I
need to edit or update a spreadsheet. The same is true when
I
want to
protect them again. Isn't there some way to do all
worksheets
at the same
time?

Protecting the workbook as a whole is not a workable
solution.
It restricts
things I don't want to restrict.

A macro generally starts with 'Sub' or 'Public Sub' or 'Private Sub' and
then finshes with 'End Sub'. There were four routines in what I posted.

If you are only protecting certain sheets then I like JE's Toggle routine
best, because you run it and it will switch each sheets protection state.
You do what you want to do and then run it again and it will toggle them all
back the way they were.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"CheriT63" wrote in message
...
I appreciate your information. I am not that familiar with using Excel
VBA.
How would I work these? And were there three different macros you listed?
I
was unable to determine where one ended and the other started.

Thanks again

"Ken Wright" wrote:

Take your pick:-

Public Sub ToggleProtect1()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
If .ProtectContents Then
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
Else
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End If
End With
Next wkSht
MsgBox Mid(statStr, 2)
End Sub



Sub Toggleprotect2()
'Courtesy of J E McGimpsey
'If only selected sheets are to be protected, then a toggle works well

Const PWORD As String = "ken"
Dim wkSht As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If sh.ProtectContents = False Then
sh.Protect PWORD
Else
sh.Unprotect PWORD
End If
Next sh
End Sub



Public Sub ProtectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Protect Password:=PWORD
statStr = statStr & ": Protected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub



Public Sub UnprotectAllSheets()
'Courtesy of J E McGimpsey
'Space allowed for insertion of a password
'Code lists every sheet with it's protection status
Application.ScreenUpdating = False
Const PWORD As String = ""
Dim wkSht As Worksheet
Dim statStr As String

For Each wkSht In ActiveWorkbook.Worksheets
With wkSht
statStr = statStr & vbNewLine & "Sheet " & .Name
wkSht.Unprotect Password:=PWORD
statStr = statStr & ": Unprotected"
End With
Next wkSht
MsgBox Mid(statStr, 2)
Application.ScreenUpdating = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03


--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)

--------------------------------------------------------------------------
--

"CheriT63" wrote in message
...
I find it tedious to have to unprotect each worksheet individually
when I
need to edit or update a spreadsheet. The same is true when I want to
protect them again. Isn't there some way to do all worksheets at the
same
time?

Protecting the workbook as a whole is not a workable solution. It
restricts
things I don't want to restrict.