validation unlocks cells

Aug 9, 2013 at 7:01 PM
Edited Aug 13, 2013 at 5:29 PM
I built an EPPlus spreadsheet with half a dozen columns which need to be locked
this code locks the cells from the columns in question:
                        ws.Cells[rRow, cColumn].Style.Locked = true;
one of the columns contains validation (not one of the locked columns)
this VBA is added to Worksheet1:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rRow As Long
Dim cColumn As Long
Dim thisCell As Range
Dim ws As Worksheet
Dim rRange As Range

rRow = Target.Row
cColumn = Target.Column
If cColumn <> 13 Then
    GoTo Exit_Sub
End If
If ThisWorkbook.Worksheets("Sheet1").Cells(rRow, 1) = "" Then
    GoTo Exit_Sub
End If

Application.EnableEvents = False
Set thisCell = Target
Set ws = ThisWorkbook.Worksheets("Sheet2")
Set rRange = ws.Range("A:A")
ActiveWorkbook.Names.Add Name:="nName", RefersTo:=rRange

With thisCell.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=nName"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "Pays de Commence"
    .ErrorTitle = "Oops Error"
    .InputMessage = "Select One"
    .ErrorMessage = "Not a valid selection"
    .ShowInput = True
    .ShowError = True
End With
Exit_Sub:
Application.EnableEvents = True
End Sub

after one of the rows is selected in this column all the locked cells are unlocked

um is this expected behavior?


this is a few days later
I was able to get the locking / unlocking to work by rearranging where I lock the worksheet
however, I can't seem to figure out how to add a ValidationList to a locked spreadsheet
unprotecting the worksheet prompts for password - and there doesn't seem to be a hook for the protection option which says apply to keyboard input only (and let VBA go through)
any assistance would be greatly appreciated