i can not test your solution at this time because i have not installed xl2010
but when i see that there are vba's solutions i'm much encouraged to upgrade my old xl,
it's very nice to you, thank you very much Garry

--
isabelle


Le 2012-02-08 23:42, GS a écrit :
Isabelle, nice catch! I forgot all about that.

If anyone's interested, the protection schema I use follows below.

I basically lets you set default protection parameters using positive nums for True,

negative nums for False. It also includes a special setting for allowing selection as the last enum value.

The settings are stored in an array which can be revised at runtime as desired,

OR can be integrated with UI settings stored in local scope defined name[s] for each sheet.

That means you can use different protection schemes for different
sheets.

(In Michael's case, he needs to change -14 to 14)

I welcome any feedback/questions...
**Watch for line wraps**

Option Explicit

'Copy the following line into Auto_Open or Workbook_Open
'to initialize the default permissions for the wkb,
'and to reset protection on all sheets at startup.
'**Note that some of the settings do not persist after the wkb is closed,
'and so reset must be done each time the wkb is opened.
'================================================= =
' Call Set_DefaultWksProtection: Protect_AllSheets
'================================================= =

'Use enum for changing option settings prior to running WksProtect,
'so you know which permissions you're changing.
'Example: gvaWksProtectOpts(WksProtection.wpAllowFormatCells ) = False
'When done, run Set_DefaultWksProtection() to restore defaults
Enum WksProtection
wpDrawingObjects = 1
wpContents = 2
wpScenarios = 3
wpUserInterfaceOnly = 4
wpAllowFiltering = 5
wpAllowFormatCols = 6
wpAllowFormatRows = 7
wpAllowFormatCells = 8
wpAllowDeleteCols = 9
wpAllowDeleteRows = 10
wpAllowInsertCols = 11
wpAllowInsertRows = 12
wpAllowInsertHLinks = 13
wpAllowPivotTables = 14
wpEnableAutoFilter = 15
wpEnableOutlining = 16
wpEnableSelection = 17 '0=xlNoRestrictions; 1=xlUnlockedCells; 2=xlNoSelection
End Enum

Public gvaWksProtectOpts(1 To 17)
Const gsDEF_WKS_PROTECTION As String = "-1,2,3,4,5,6,7,8,-9,-10,-11,-12,-13,-14,15,16,0"


Sub Set_DefaultWksProtection()
Dim i As Integer, vwpOpts As Variant
vwpOpts = Split(gsDEF_WKS_PROTECTION, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1
gvaWksProtectOpts(i) = (vwpOpts(i - 1) 0)
Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) = vwpOpts(UBound(vwpOpts))
End Sub

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:=gvaWksProtectOpts(WksProtection.wp DrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpConten ts), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScena rios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection .wpUserInterfaceOnly), _
AllowFiltering:=gvaWksProtectOpts(WksProtection.wp AllowFiltering), _
AllowFormattingColumns:=gvaWksProtectOpts(WksProte ction.wpAllowFormatCols), _
AllowFormattingRows:=gvaWksProtectOpts(WksProtecti on.wpAllowFormatRows), _
AllowFormattingCells:=gvaWksProtectOpts(WksProtect ion.wpAllowFormatCells), _
AllowDeletingColumns:=gvaWksProtectOpts(WksProtect ion.wpAllowDeleteCols), _
AllowDeletingRows:=gvaWksProtectOpts(WksProtection .wpAllowDeleteRows), _
AllowInsertingColumns:=gvaWksProtectOpts(WksProtec tion.wpAllowInsertCols), _
AllowInsertingRows:=gvaWksProtectOpts(WksProtectio n.wpAllowInsertRows), _
AllowInsertingHyperlinks:=gvaWksProtectOpts(WksPro tection.wpAllowInsertHLinks), _
AllowUsingPivotTables:=gvaWksProtectOpts(WksProtec tion.wpAllowPivotTables)
Else
.Protect Password:=PWRD, _
DrawingObjects:=gvaWksProtectOpts(WksProtection.wp DrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpConten ts), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScena rios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection .wpUserInterfaceOnly)
End If
.EnableAutoFilter = gvaWksProtectOpts(WksProtection.wpEnableAutoFilter )
.EnableOutlining = gvaWksProtectOpts(WksProtection.wpEnableOutlining)

Select Case CLng(gvaWksProtectOpts(WksProtection.wpEnableSelec tion))
Case 0: .EnableSelection = xlNoRestrictions '0
Case 1: .EnableSelection = xlUnlockedCells '1
Case 2: .EnableSelection = xlNoSelection '-4142
End Select
End With
End Sub 'wksProtect()

Sub wksUnprotect(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
Wks.Unprotect PWRD
End Sub

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

Sub Protect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: ResetProtection Wks: Next
Application.ScreenUpdating = True
End Sub

Sub Unprotect_AllSheets()
Dim Wks As Worksheet
Application.ScreenUpdating = False
For Each Wks In ThisWorkbook.Worksheets: wksUnprotect Wks: Next
Application.ScreenUpdating = True
End Sub