Protect/unprotect all worksheets

[Guest]

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.

 

[William]

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

(e-mail address removed)

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

 

[Guest]

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