Help w/ modifying code... pull down menu
 

I'm trying to modify the following code so that instead of the cells
populating the "next column and down" when selecting from the pull down menu,
it populates the cell beneath the pull down menu. Any help/tips/suggestions
where to look, is greatly appreciated. (I apologize in advance if this
question seems simple but my skillset in this area is practically
non-existent).

Laura

Option Explicit
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, 12, 14, 18
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 Select

End If

exitHandler:
Application.EnableEvents = True

End Sub

Help w/ modifying code... pull down menu
 

Change

lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value

to

lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Laura L" wrote in message
...
I'm trying to modify the following code so that instead of the cells
populating the "next column and down" when selecting from the pull down
menu,
it populates the cell beneath the pull down menu. Any
help/tips/suggestions
where to look, is greatly appreciated. (I apologize in advance if this
question seems simple but my skillset in this area is practically
non-existent).

Laura

Option Explicit
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, 12, 14, 18
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 Select

End If

exitHandler:
Application.EnableEvents = True

End Sub

Help w/ modifying code... pull down menu
 

Thank you so much. It works.

Is there a way to add a line that would allow you to click on a cell below
the pull-down and then choose from the pull down? Right now, if after you've
made selections you click on a different cell and then you go back to
continue with the list, the code doesn't work any more... is there any way to
fix this?

Laura

"Bob Phillips" wrote:

Change

lRow = Cells(Rows.Count, lCol + 1).End(xlUp).Row + 1
End If
Cells(lRow, lCol + 1).Value = Target.Value

to

lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Laura L" wrote in message
...
I'm trying to modify the following code so that instead of the cells
populating the "next column and down" when selecting from the pull down
menu,
it populates the cell beneath the pull down menu. Any
help/tips/suggestions
where to look, is greatly appreciated. (I apologize in advance if this
question seems simple but my skillset in this area is practically
non-existent).

Laura

Option Explicit
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, 12, 14, 18
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 Select

End If

exitHandler:
Application.EnableEvents = True

End Sub