View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Laura L[_2_] Laura L[_2_] is offline
external usenet poster
 
Posts: 10
Default 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