Protect/unprotect all worksheets
 

Is there a way to easily unprotect all the worksheets in a workbook? For
example, I have a workbook with 20 worksheets (one for each employee). The
worksheets have been individually protected (except for the select cells into
which I enter data. Now with the new year, I would like to unprotect all the
worksheets so I can change the (previously protected) field that contains
the year and then reprotect all the worksheets. Thanks.

Hi Janna

Sub UnprotectAll()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Unprotect Password:="whatever"
Next ws
End Sub

Sub ProtectAll()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Protect Password:="whatever"
Next ws
End Sub


--
XL2002
Regards

William



"Janna" wrote in message
...
| Is there a way to easily unprotect all the worksheets in a workbook? For
| example, I have a workbook with 20 worksheets (one for each employee).
The
| worksheets have been individually protected (except for the select cells
into
| which I enter data. Now with the new year, I would like to unprotect all
the
| worksheets so I can change the (previously protected) field that contains
| the year and then reprotect all the worksheets. Thanks.

It would depend on if the password is the same in all worksheets, or you have
a list you can reference.
Substitute your password for "password" in the following code

Public pwd As String

Sub UnProtectSheet()
pwd = "password"
Worksheets("sheet1").Unprotect Password:=pwd
End Sub

Sub ProtectSheet()
pwd = "password"
Worksheets("sheet1").Protect Password:=pwd
End Sub

Sub UnptAllSheets()
For Each s In ActiveWorkbook.Worksheets
UnProtectSheet
Next s
End Sub

Sub ptAllSheets()
For Each s In ActiveWorkbook.Worksheets
ProtectSheet
Next s
End Sub

Bear

"Janna" wrote:

Is there a way to easily unprotect all the worksheets in a workbook? For
example, I have a workbook with 20 worksheets (one for each employee). The
worksheets have been individually protected (except for the select cells into
which I enter data. Now with the new year, I would like to unprotect all the
worksheets so I can change the (previously protected) field that contains
the year and then reprotect all the worksheets. Thanks.