multiple choices from pulldown... how to apply to several pulldown

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
 
T

Tom Ogilvy

change
If Target.Column = lCol Then

to
If Target.Column = 7 or Target.column = 12 or _
Target.column = 14 or Target.column = 18 then
Icol = Target.column
 
D

DianeN

Hi Tom, thanks so much. I guess when I pasted the code, the "1col" and
"1row" came out looking like "I" or something... or are you saying I need
to change them from "1" to "I" ?

Sorry to be dense. When I copied and pasted your code below, it didn't give
me an error message, it just didn't work on any of the columns.
Thanks
 
D

DianeN

Tom, nevermind. I changed the "1" to "i" on both row and column and it works!!
Thanks so much!
 
T

Tom Ogilvy

I wasn't suggesting that you change 1 to I, as you are correct, the original
looked like I's to me. But if you have changed them and it works, then no
reason to change back that I can think of.
 

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