D
DianeN
Hi,
Follow up: I first added to original question, but it's really a new
question.
After using the code Debra was kind enough to share, I need to apply the
action (choose from a pull down menu and place each entry
into a new row) to 4 or 5 specific columns. I tried the following but it
didn't work.(see row surrounded by *****)
Thanks for any help.
Here's the code: (the line with all the stars is what I tried).
------
Public Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
********lCol = 7 or 14 or 18 or 12 'column with data validation cell****
(I also tried using "And")
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
Application.EnableEvents = False
If Target.Column = lCol Then
If Target.Value = "" Then GoTo exitHandler
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
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Follow up: I first added to original question, but it's really a new
question.
After using the code Debra was kind enough to share, I need to apply the
action (choose from a pull down menu and place each entry
into a new row) to 4 or 5 specific columns. I tried the following but it
didn't work.(see row surrounded by *****)
Thanks for any help.
Here's the code: (the line with all the stars is what I tried).
------
Public Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
********lCol = 7 or 14 or 18 or 12 'column with data validation cell****
(I also tried using "And")
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
Application.EnableEvents = False
If Target.Column = lCol Then
If Target.Value = "" Then GoTo exitHandler
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
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub