Macros to protect/unprotect multiple worksheets

Posted by Dwight on June 15, 2001 1:19 PM

Need a macro to unprotect multiple active worksheets and one to protect multiple active worksheets. I have a workbook with 12 sheets which I want very much to keep protected but which also needs frequent modification and it's a real pain to unprotect 12 sheets one at a time.
Can someone help?

Posted by GregC on June 15, 2001 2:03 PM

Here's the code to protect:

sheetcount = Worksheets.Count
For i = 1 To sheetcount
ActiveSheet.Protect
Next i

Here's the code to unprotect:

sheetcount = Worksheets.Count

For i = 1 To sheetcount
ActiveSheet.unProtect
Next i

Posted by Dwight on June 15, 2001 2:45 PM

Greg: Doesn't work; could I have missed something

Greg:
Tried for both protect and unprotect & got similar "runtime error '1004', "Unprotect method of Worksheet class failed" messages for both. Could I have missed something? I will paste complete code below in case. It does unprotect the active sheet when only one sheet is active, bit I was hoping for something that would work with multiple sheets.
Thanks.

Sub UnprotectActiveSheets()
'
' UnprotectActiveSheets Macro
' Macro recorded 6/15/01 by Dwight Hamilton
sheetcount = Worksheets.Count
For i = 1 To sheetcount
ActiveSheet.Unprotect
Next i

End Sub

Posted by Barrie Davidson on June 15, 2001 2:50 PM

Re: Greg: Doesn't work; could I have missed something

UnprotectActiveSheets Macro Macro recorded 6/15/01 by Dwight Hamilton

Dwight, you need to declare the variable "sheetcount". Put this before "sheetcount = Worksheets.Count".
Dim sheetcount As Integer

Barrie

Posted by Paul on June 15, 2001 5:14 PM

Not a macro but you might want to look at this add in, it will do what you want and much more http://www.asap-utilities.com/

Posted by Ivan F Moala on June 15, 2001 5:14 PM

Re: Greg: Doesn't work; could I have missed something

Dwights Macro needs a slight mode to get it working.
Barrie is correct in his comments.
BUT......this macro will only protect the active sheet. Modify as follows......

Dim sheetcount As Integer
Dim i As Integer

Sub ProtectSh()
sheetcount = Worksheets.Count
For i = 1 To sheetcount
Sheets(i).Protect
Next i
End Sub
'Here 's the code to unprotect:

Sub UnprotectSh()

sheetcount = Worksheets.Count

For i = 1 To sheetcount
Sheets(i).Unprotect
Next i
End Sub

Ivan