You can't 'add' protection to a protected sheet. The
'UserInterfaceOnly' parameter does not persist between sessions and so
protect must be *reset* on all protected sheets that you want code to
be able to modify without having to toggle protection on/off every time
you need to programmatically edit cells. It's better to reset
protection at startup so it only needs doing once per session,
obviating the need for redundant processing during the rest of the
project's runtime session.
You can specify '.EnableAutoFilter' on any sheet after it's protected.
Same goes for '.EnableOutlining'.
I use a consistent methodology for protection so I can be flexible
about how protection is applied in my projects. This allows me to use
'default' protection permissions for all sheet OR sheet-specific
permissions as required per project. When using the latter I store the
protection settings in a local defined name ("uiProtect") for each
sheet where 'special' permissions are needed. This allows me the
flexibility to customize protection via my "SetupUI" routine according
to the stored settings by passing them to my "Edit_WksProtection"
routine prior to setting the target sheet's protection permissions. Of
course, this is followed by running the "Reset_WksProtection" procedure
afterwards so it's always in 'default' mode.
Here's the entire module code... (watch for line wraps)
Option Explicit
'Copy the following line into Aut
pen 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_DefaultProtectionOpts: Protect_AllSheets
'==================================================
'Use enum for changing option settings so you can apply 'special'
permissions to
'specific sheets and know which permissions you're changing prior to
running WksProtect.
'Example: gvaWksProtectOpts(WksProtection.wpAllowFormatCells) = False
'When done, run 'Set_DefaultProtectionOpts()' 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
'Permissions Settings
'This const is to be configured to the desired default protection
permissions for a project.
'These permissions are Boolean values and so this is indicated by the
corresponding value being negative for FALSE, positive for TRUE.
'**Exception** 'wpEnableSelection' is always a positive value between
0 and 2
Const gsDEF_WKS_PROTECTION As String =
"-1,2,3,4,5,6,7,8,-9,-10,-11,-12,-13,-14,15,16,0"
Public gvaWksProtectOpts(1 To 17) 'Holds sheet protection
permission settings
Enum WkbProtection
Structure = 1: Windows = 2
End Enum
Const gsDEF_WKB_PROTECTION As String = "1,-2"
Public gvaWkbProtectOpts(1 To 2) 'Holds workbook protection
permission settings
Public Const g_szPWRD As String = " "
Sub Set_DefaultProtectionOpts()
Dim i As Integer, vProtectOpts As Variant
'Worksheet settings
vProtectOpts = Split(gsDEF_WKS_PROTECTION, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1:
gvaWksProtectOpts(i) = (vProtectOpts(i - 1) > 0): Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) =
vProtectOpts(UBound(vProtectOpts))
'Workbook settings
vProtectOpts = Split(gsDEF_WKB_PROTECTION, ",")
For i = LBound(gvaWkbProtectOpts) To UBound(gvaWkbProtectOpts):
gvaWkbProtectOpts(i) = (vProtectOpts(i - 1) > 0): Next
End Sub
Sub wksProtect(Optional wks As Worksheet)
' Protects specified sheets according to Excel version.
' Assumes Public Const g_szPWRD as String contains the password, even
if there isn't one.
If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
With wks
If Val(Application.Version) >= 10 Then
.Protect Password:=g_szPWRD, _
DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly),
_
AllowFiltering:=gvaWksProtectOpts(WksProtection.wpAllowFiltering), _
AllowFormattingColumns:=gvaWksProtectOpts(WksProtection.wpAllowFormatCols),
_
AllowFormattingRows:=gvaWksProtectOpts(WksProtection.wpAllowFormatRows),
_
AllowFormattingCells:=gvaWksProtectOpts(WksProtection.wpAllowFormatCells),
_
AllowDeletingColumns:=gvaWksProtectOpts(WksProtection.wpAllowDeleteCols),
_
AllowDeletingRows:=gvaWksProtectOpts(WksProtection.wpAllowDeleteRows),
_
AllowInsertingColumns:=gvaWksProtectOpts(WksProtection.wpAllowInsertCols),
_
AllowInsertingRows:=gvaWksProtectOpts(WksProtection.wpAllowInsertRows),
_
AllowInsertingHyperlinks:=gvaWksProtectOpts(WksProtection.wpAllowInsertHLinks),
_
AllowUsingPivotTables:=gvaWksProtectOpts(WksProtection.wpAllowPivotTables)
Else
.Protect Password:=g_szPWRD, _
DrawingObjects:=gvaWksProtectOpts(WksProtection.wpDrawingObjects), _
Contents:=gvaWksProtectOpts(WksProtection.wpContents), _
Scenarios:=gvaWksProtectOpts(WksProtection.wpScenarios), _
UserInterfaceOnly:=gvaWksProtectOpts(WksProtection.wpUserInterfaceOnly)
End If
.EnableAutoFilter =
gvaWksProtectOpts(WksProtection.wpEnableAutoFilter)
.EnableOutlining =
gvaWksProtectOpts(WksProtection.wpEnableOutlining)
Select Case gvaWksProtectOpts(WksProtection.wpEnableSelection)
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 g_szPWRD
End Sub
Sub Reset_WksProtection(Optional wks As Worksheet)
' Use at startup to re-apply non-persistent permissions
If wks Is Nothing Then Set wks = ActiveSheet
wks.Unprotect g_szPWRD
wksProtect wks
End Sub
Sub Edit_WksProtection(Settings As String)
' Use to set sheet-specific permissions
Dim i As Integer, vProtectOpts As Variant
vProtectOpts = Split(Settings, ",")
For i = LBound(gvaWksProtectOpts) To UBound(gvaWksProtectOpts) - 1:
gvaWksProtectOpts(i) = (vProtectOpts(i - 1) > 0): Next
gvaWksProtectOpts(WksProtection.wpEnableSelection) =
vProtectOpts(UBound(vProtectOpts))
End Sub
Sub Protect_AllSheets()
' Use to re-apply non-persistent permissions to all sheets belonging to
ThisWorkbook
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets: Reset_WksProtection wks:
Next
Application.ScreenUpdating = True
End Sub
Sub Unprotect_AllSheets()
' Use remove permissions from all sheets belonging to ThisWorkbook
Dim wks As Worksheet
Application.ScreenUpdating = False
For Each wks In ThisWorkbook.Worksheets: wksUnprotect wks: Next
Application.ScreenUpdating = True
End Sub
Sub wkbProtect(Optional Wkb As Workbook)
' Protects specified workbook.
' Assumes Public Const g_szPWRD as String contains the password, even
if there isn't one.
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
On Error Resume Next
Wkb.Protect g_szPWRD, gvaWkbProtectOpts(WkbProtection.Structure),
gvaWkbProtectOpts(WkbProtection.Windows)
End Sub
Sub Reset_WkbProtection(Optional Wkb As Workbook)
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
On Error Resume Next
Wkb.Unprotect g_szPWRD
wkbProtect Wkb
End Sub
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
