cosmmarchy expressed precisely :
Hi,
I have a small piece of VBA which adds custom data validation on certain
cells in my worksheet. These cells are not locked or hidden but when I
protect the worksheet, this code does not work.
There does not appear to be any options to allow this code to work when
I protect the sheet and I do not have a password on it.
Has anyone encountered this before? Could someone point me in the right
direction?
Many thanks
Typically, code will not work on protected sheets unless the protection
parameter 'UserInterfaceOnly' is set 'True'! Unfortunately, this is a
non-persistent setting meaning it will not have effect next time the
file is opened unless you reset protect when it opens. Resetting
protection requires existing protection be removed, then re-applied.
You can accomplish this via the Workbook_Open() event behind the
'ThisWorkbook' object, OR by using an Aut
pen() procedure in a
standard module. I prefer the latter but this is just my preference
because there are issues with using Workbook_Open() or
Workbook_BeforeClose() if your file becomes corrupt.
Here's some sample code I typically use in a standard module named
"_mOpenClose", which I use in all my VBA projects...
Option Explicit
Const PWRD As String = "password"
Sub Aut
pen()
StoreExcelSettings: MakeMenus: SetupUI: ProtectAllSheets
End Sub 'Aut
pen
Sub Auto_Close()
RemoveMenus: RestoreExcelSettings: RestoreUI
End Sub 'Auto_Close
Sub ProtectAllSheets(Optional Wkb As Workbook)
Dim wks As Worksheet
If Wkb Is Nothing Then Set Wkb = ActiveWorkbook
For Each wks In Wkb.Worksheets: ResetProtection wks: Next 'wks
End Sub 'ProtectAllSheets
Sub ResetProtection(Optional Wks As Worksheet)
If Wks Is Nothing Then Set Wks = ActiveSheet
Wks.Unprotect PWRD: wksProtect Wks
End Sub 'ResetProtection
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: Wks [In] Optional. Ref to the sheet to be protected.
' (Defaults to ActiveSheet if missing)
If Wks Is Nothing Then Set Wks = ActiveSheet
On Error Resume Next
With Wks
If Val(Application.VERSION) >= 10 Then
'Copy/paste the desired parameters above the commented line.
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True ', _
AllowDeletingColumns:=True, _
AllowDeletingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowInsertingRows:=True, _
AllowUsingPivotTables:=True
Else
.Protect Password:=PWRD, _
DrawingObjects:=False, Contents:=True, Scenarios:=True,
UserInterfaceOnly:=True
End If
' .EnableAutoFilter = True
' .EnableOutlining = True
' .EnableSelection = xlNoRestrictions
.EnableSelection = xlUnlockedCells
' .EnableSelection = xlNoSelection
End With
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
