Worksheet Change Code Not Firing 1

[EliseFreedman]

Hi There

I am trying to allow a user to select multiple values from a data validation list. The values should be copied to the next empty row in an adjacent column. However, the code does not seem to be firing. Can anyone advise what im doing wrong. I have tried adding a couple of msgboxes but nothing happens which makes me think the code is not even running when the user selects something from the list

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler

Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
lCol = Target.Column 'column with data validation cell
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
   'do nothing
Else
    If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Select Case Target.Column
       Case 7, 8, 9, 10, 11, 12, 14, 15, 16, 17, 18, 19, 20
        If Target.Offset(0, 1).Value = "" Then
           lRow = Target.Row
        Else
           lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
        End If
        Cells(lRow, lCol + 1).Value = Target.Value
        Target.ClearContents
    End Select
   
End If

exitHandler:
  Application.EnableEvents = True
  
End Sub

 

[combo]

Have you tried to add a breakpoint to the beginning of your procedure, display locals window, execute step by step the code and observe program flow?

combo

 

[SkipVought]

Elsie,

Make sure that Application.EnableEvents is TRUE before you attempt to run this event. You may have inadvertently left it FALSE.

The code does run.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[EliseFreedman]

Skip to the rescue as usual

 

[EliseFreedman]

Is there anyway to use this code but have it so that it only fires for one data validation list in the worksheet but not to fire for the other data validation lists that are on the same worksheet (same column but different rows)

 

[Deniall]

You can use the "Target" argument (as in "ByVal Target As Range") to detect which cell was changed to trigger the call, then make your subsequent actions take account of this.