| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parsing after multiple selection in drop-down?
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
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 "DianeN" wrote: 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
"dkinn" wrote: 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 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 just a thought David "DianeN" wrote: 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
I've updated the sample file to include an example of copying values to
multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
Debra, you are awesome! Thank you!
"Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
You're welcome!
DianeN wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
I like your code, you've got some good examples up there
Thanks David "DianeN" wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
David, thanks so much for helping!
"dkinn" wrote: I like your code, you've got some good examples up there Thanks David "DianeN" wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Parsing after multiple selection in drop-down?
Thanks!
dkinn wrote: I like your code, you've got some good examples up there Thanks David "DianeN" wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more ? re Parsing after multiple selection in drop-down?
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 "Debra Dalgleish" wrote: Thanks! dkinn wrote: I like your code, you've got some good examples up there Thanks David "DianeN" wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
One more ? re Parsing after multiple selection in drop-down?
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' DianeN wrote: 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 "Debra Dalgleish" wrote: Thanks! dkinn wrote: I like your code, you've got some good examples up there Thanks David "DianeN" wrote: Debra, you are awesome! Thank you! "Debra Dalgleish" wrote: I've updated the sample file to include an example of copying values to multiple rows: http://www.contextures.com/excelfiles.html Under Data Validation, look for 'DV0017 - Select Multiple Items from Dropdown List' DianeN wrote: 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. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple selection drop down list | Excel Worksheet Functions | |||
| Drop Down - Multiple Items Selection | Excel Discussion (Misc queries) | |||
| Possible to create multiple-selection drop-down box in Excel? | Excel Worksheet Functions | |||
| Can I set up a multiple selection drop down list? | New Users to Excel | |||
| How to allow multiple selection capability in excel drop down lis. | Excel Discussion (Misc queries) | |||
Linear Mode
