ExcelBanter - View Single Post - Is there any way that you can protect or unprotect a group of wor.

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.