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

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 Auto_Open() 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 Auto_Open()
StoreExcelSettings: MakeMenus: SetupUI: ProtectAllSheets
End Sub 'Auto_Open

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

Please post the smallest sample that does not work.

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


On Thursday, June 14, 2012 5:55:46 AM UTC-4, cosmmarchy wrote:
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




--
cosmmarchy

Hi, attached is a stripped down example.

What should happen is when you click on the bordered column B cells the data validation is set up for that cell and the column C cell validation should be setup when that is clicked.

What actually happens is with the protection enabled, any previously clicked cell will retain any validation but a newly clicked cell will not be able to have any validation setup because it is locked.

The protection code is of that above so the password is 'password'!!

Thanks

Sounds to me like you want 'conditional' validation in colC based on
selection in colB. Have a look at...

http://www.contextures.com/xlDataVal02.html

--
Garry

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