I think that the safest thing to try is to record a macro when you protect a
worksheet and toggle the settings that you want checked.

You'll see the code you need to add to your code.

If you have trouble incorporating the changes, post the recorded macro code and
I'm sure you'll get help.

Colin Hayes wrote:

Hi

I use this macro to protect and unprotect all the worksheets in my
workbook :

Protect_Unprotect Macro
' Shortcut Ctrl + z
' Protects / Unprotects by turn all sheets in a workbook

'
Const PWORD As String = "12071956"
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

it works very well , but unfortunately resets all the protection
parameters to the minimum. I need to add a line to it so that it will
maintain formatting of rows in the protected sheet.

AllowFormattingRows:=True

but I'm not sure where i can incorporate it into the code. Can someone
help?

Thanks.

--

Dave Peterson