Here's one example (using SheetProtection) of how I normally handle
routines that I want this kind of flexibility with, that you may get
some ideas from for how to structure your project...

Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: wksProtect Wks
End Sub

Sub Protect_AllSheets(Optional Wkb As Workbook)
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets: ResetProtection Wks: Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets(Optional Wkb As Workbook)
Dim Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets: wksUnprotect Wks: Next
Application.ScreenUpdating = True
End Sub

...and the main process routine being called by the above...

Sub wksProtect(Optional Wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const PWRD as String contains the password, even if
there isn't one.
'
' Arguments: WksName [In] Optional. The name of the sheet to be
protected.
' Defaults to ActiveSheet.Name if missing.

If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) = 10 Then
.Protect Password:=PWRD, _

DrawingObjects:=CBool(gvWksProtection(WksProtectio n.wpDrawingObjects)),
_
Contents:=CBool(gvWksProtection(WksProtection.wpCo ntents)), _
Scenarios:=CBool(gvWksProtection(WksProtection.wpS cenarios)), _

UserInterfaceOnly:=CBool(gvWksProtection(WksProtec tion.wpUserInterfaceOnly)),
_

AllowFiltering:=CBool(gvWksProtection(WksProtectio n.wpAllowFiltering)),
_

AllowFormattingColumns:=CBool(gvWksProtection(WksP rotection.wpAllowFormatCols)),
_

AllowFormattingRows:=CBool(gvWksProtection(WksProt ection.wpAllowFormatRows)),
_

AllowFormattingCells:=CBool(gvWksProtection(WksPro tection.wpAllowFormatCells)),
_

AllowDeletingColumns:=CBool(gvWksProtection(WksPro tection.wpAllowDeleteCols)),
_

AllowDeletingRows:=CBool(gvWksProtection(WksProtec tion.wpAllowDeleteRows)),
_

AllowInsertingColumns:=CBool(gvWksProtection(WksPr otection.wpAllowInsertCols)),
_

AllowInsertingRows:=CBool(gvWksProtection(WksProte ction.wpAllowInsertRows)),
_

AllowInsertingHyperlinks:=CBool(gvWksProtection(Wk sProtection.wpAllowInsertHLinks)),
_

AllowUsingPivotTables:=CBool(gvWksProtection(WksPr otection.wpAllowPivotTables))
Else
.Protect Password:=PWRD, _

DrawingObjects:=CBool(gvWksProtection(WksProtectio n.wpDrawingObjects)),
_
Contents:=CBool(gvWksProtection(WksProtection.wpCo ntents)), _
Scenarios:=CBool(gvWksProtection(WksProtection.wpS cenarios)), _

UserInterfaceOnly:=CBool(gvWksProtection(WksProtec tion.wpUserInterfaceOnly))
End If
.EnableAutoFilter =
CBool(gvWksProtection(WksProtection.wpEnableAutoFi lter))
.EnableOutlining =
CBool(gvWksProtection(WksProtection.wpEnableOutlin ing))
.EnableSelection =
CLng(gvWksProtection(WksProtection.wpEnableSelecti on))
End With 'Wks
End Sub 'wksProtect()

...which you can also call directly from any routine.

HTH

Note that the main routine uses Enum elements which I also can access
individually for each sheet by storing its protection settings in a
defined name as shown here...

Sub Set_EachWksProtection(Optional Wkb As Workbook)
' This applies sheet-specific protection as stored
' in the sheet's local scope defined name "uiProtect".
Dim vSettings, Wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
Application.ScreenUpdating = False
For Each Wks In Wkb.Worksheets
vSettings = Empty
On Error Resume Next
vSettings = Wks.Names("uiProtect").RefersTo
On Error GoTo 0
If Not (vSettings = Empty) Then
vSettings = Application.Evaluate("'" & Wks.name & "'!uiProtect")
gvWksProtection = Split(vSettings, ",")
wksProtect Wks
End If
Next 'wks
Application.ScreenUpdating = True: Set_DefaultWksProtection
End Sub

...and the supporting routine follows.

Sub Set_DefaultWksProtection()
' Use to reset to default params
gvWksProtection = Split(gsDEF_WKS_PROTECTION, ",")
End Sub

The declaration defs used...

Enum WksProtection
wpDrawingObjects '0
wpContents '1
wpScenarios '2
wpUserInterfaceOnly '3
wpAllowFiltering '4
wpAllowFormatCols '5
wpAllowFormatRows '6
wpAllowFormatCells '7
wpAllowDeleteCols '8
wpAllowDeleteRows '9
wpAllowInsertCols '10
wpAllowInsertRows '11
wpAllowInsertHLinks '12
wpAllowPivotTables '13
wpEnableAutoFilter '14
wpEnableOutlining '15
wpEnableSelection '16: 0=xlNoRestrictions; 1=xlUnlockedCells;
-4142=xlNoSelection
End Enum

Public gvWksProtection
Const gsDEF_WKS_PROTECTION As String =
"0,1,2,3,4,5,6,7,-8,-9,-10,-11,-12,-13,14,15,0"

...where the above Constant is the default used most commonly by my
multi-sheet projects. The application concept is simple: positive
numbers CBool as 'True', negative numbers as 'False'! The main routine
processes all protection options I'd likely use in a project.

The ResetProtection routine is used to re-apply non-persistent options
at startup. As you can see, it gives me the option to go with the
default settings or use sheet-specific settings when working with all
sheets in a workbook. It also gives me the option to edit the 'active'
settings stored in the gvWksProtection array and then apply it to a
specific sheet (or sheets) or just the active sheet 'on-the-fly'.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion