Parsing after multiple selection in drop-down?

D

DianeN

Hi,
Using some great code from the "Contextures" website, I'm able to choose
multiple items from a drop-down list, then have them all appear in the same
cell (separated by commas. ) E.g., 4 items might be selected, and all
appear in cell G8:
Commercial, DDA, Treasury, Cash

Now I need to parse them and place each one in the next row down. I.e.,
'Commercial' in cell G8
'DDA' in cell G9
'Treasury' in cell G10
'Cash' in cell G11

Thanks for any help.
 
D

dkinn

If you could post the code you are using to get the items out of the list and
into the cell, it might be easier to modify that code than to have to start
over and parse out the cell content and move it to separate cells.

I'd be happy to take a look at your code

just a thought

David
 
D

DianeN

dkinn said:
If you could post the code you are using to get the items out of the list and
into the cell, it might be easier to modify that code than to have to start
over and parse out the cell content and move it to separate cells.

I'd be happy to take a look at your code
Click to expand...
Here it is, and thanks very much

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
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
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 7 Or 11 Or 15 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
 
D

DianeN

one more 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.
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
 
D

Debra Dalgleish

You can make lCol = Target.column, then use Select Case to test for the
columns of interest.
I've updated the sample file for multiple rows, so it works for data
validation dropdowns in several columns:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0017 - Select Multiple Items from
Dropdown List'
 

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

Similar Threads

Pick from drop-down list gives empty or erroneous result 6
problems when removing validation 1
Drop Down List 5
Developer help: autofilling selections 0
Print Macro w/drop down list 1
Drop Down Selection Assistance 2
Drop Down List - Other Cell Results 5
assign Drop Down selections with values 1

Top