Pull Down Menu - Code not working

L

Laura L

I'm trying to modify the code below so that 1) when using the pull down menu,
it populates the cell below it , and 2) it only applies to one column. Right
now, when you use the pull down menu it just populates that same cell, not
the one below it. Any help is greatly appreciated.

Thanks,
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
If Target.Offset(0, 1).Value = "" Then
lRow = Target.Row
Else
lRow = Cells(Rows.Count, lCol).End(xlUp).Row + 1
End If
Cells(lRow, lCol).Value = Target.Value
End Select

End If

exitHandler:
Application.EnableEvents = True

End Sub
 
B

Bernie Deitrick

Laura,

I tried to code what your words were describing, not what your code was seeming to do...


Private Sub Worksheet_Change(ByVal Target As Range)

Dim myVal As Variant

If Target.Value = "" Then Exit Sub
If Target.Column <> 7 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, _
Cells.SpecialCells(xlCellTypeAllValidation)) _
Is Nothing Then Exit Sub

myVal = Target.Value
Application.EnableEvents = False
Application.Undo
Target.Offset(1, 0).Value = myVal
Application.EnableEvents = True

End Sub


HTH,
Bernie
MS Excel MVP
 
L

Laura L

Hi, this does help but I also need it to populate each row beneath the pull
down menu, that's why I thought I needed the "+1" string. How do I add that
to it?

Thanks again!
 
B

Bernie Deitrick

Laura,

The line

Target.Offset(1, 0).Value = myVal

does just that - it fills in the row below the cell with the pulldown.

(Though it would be just as easy to fill in that cell directly...)

So, I'm not sure what you want besides that...

HTH,
Bernie
MS Excel MVP
 
L

Laura L

Hi Bernie,
How do you get it to populate cells beneath the pull down? Not just the cell
below it? (one at a time)

Laura
 
L

Laura L

Hi Bernie,
If the pull down menu is in G3, I need to be able to use the pull down for
all the cells below G3, not just G4.... is it possible?
 
B

Bernie Deitrick

Laura,

Try

Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal

to fill in the first empty cell below G3 (G4, then G5, then G6, etc.)

HTH,
Bernie
MS Excel MVP
 
L

Laura L

THANK YOU!!!!!!

Bernie Deitrick said:
Laura,

Try

Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal

to fill in the first empty cell below G3 (G4, then G5, then G6, etc.)

HTH,
Bernie
MS Excel MVP
 
L

Laura L

THANK YOU!!!!!! Awesome!

Bernie Deitrick said:
Laura,

Try

Cells(Rows.Count, 7).End(xlUp)(2).Value = myVal

to fill in the first empty cell below G3 (G4, then G5, then G6, etc.)

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top