Drop-down list with multiple entries in a single cell

Innoguide

Board Regular
Joined
Sep 13, 2011
Messages
159
Is there anyway, I can create a drop down list that allows users to choose items from a list and place them into a single cell with comma separation (e.g. Rome, New York, Rio Di Janero)

If I could limit it to one item per cell, a simple drop down data validation list would work, but I need to limit entry to 1 cell/column.

Any suggestions would be much appreciated.
 
I had previously made two separate suggestions. Sorry I didn't make that clear.

Suggestion 1 was to make a UserForm.
Suggestion 2 was to use a Data Validation drop down list and use the code I provided to concatenate multiple selections from that list. The code I previously provided assumed the Data Validation drop down list was in cell M10.

------------

This is how to make the user form solution. I think it's probably the better way to do it for your situation.

Create a userform with two controls on it; a ListBox and a command button.
ListBox1
CommandButton1

Put this code in the UserForm's code module...
Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()

    [COLOR=darkblue]With[/COLOR] ListBox1
        .List = Range("CL4:CL103").Value    [COLOR=green]'Range of cells with the list of cities[/COLOR]
        .MultiSelect = fmMultiSelectMulti
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    UserForm1.Caption = "Select Cities"
    CommandButton1.Caption = "Okay"
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()

    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR], strTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]With[/COLOR] ListBox1
        [COLOR=darkblue]For[/COLOR] i = 0 [COLOR=darkblue]To[/COLOR] .ListCount - 1
            [COLOR=darkblue]If[/COLOR] .Selected(i) [COLOR=darkblue]Then[/COLOR] strTemp = strTemp & .List(i) & ", "
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(strTemp) [COLOR=darkblue]Then[/COLOR]
        strTemp = Left(strTemp, Len(strTemp) - 2)
        ActiveCell.Value = strTemp
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    Unload Me
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Put this in the worksheet module. Right-click on the sheet tab and select View Code. Paste the code below VBA Edit window.
Code:
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_SelectionChange([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'Range of cells to have the pop-up userform[/COLOR]
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("D15:D314"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            UserForm1.Show
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Hallo AlphaFrog I did the same like you suggest to Innoguide and it worked very good but I need to make more drop down lists in diffrent cells with diffrent data in one worksheet.
UserForm code (my data is in a diffrent sheet named "DropDownData)

Rich (BB code):
Rich (BB code):
Private Sub UserForm_Initialize()


    With ListBox1
        .List = Range("DropDownData!G2:G16").Value    'Range of cells with the list of krone
        .MultiSelect = fmMultiSelectMulti
    End With
    
    UserForm1.Caption = "Select Krone"
    CommandButton1.Caption = "Okay"
    
End Sub




Private Sub CommandButton1_Click()


    Dim i As Long, strTemp As String
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then strTemp = strTemp & .List(i) & ", "
        Next i
    End With
    If Len(strTemp) Then
        strTemp = Left(strTemp, Len(strTemp) - 2)
        ActiveCell.Value = strTemp
    End If


Worksheet code (worksheet name "DropDown")
Rich (BB code):
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count = 1 Then
        'Range of cells to have the pop-up userform
        If Not Intersect(Range("H2:H755"), Target) Is Nothing Then
            UserForm1.Show
        End If
    End If
End Sub


So what do I need to do to have more drop down list with diffrent data in diffrent cells (in worksheet "DropDown" from I to M)

Thank you for your help! :)
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
So what do I need to do to have more drop down list with diffrent data in diffrent cells (in worksheet "DropDown" from I to M)

Change this...
If Not Intersect(Range("H2:H755"), Target) Is Nothing Then

To this...
If Not Intersect(Range("I:M"), Target) Is Nothing Then

Otherwise, I don't understand what you're asking.
 
Last edited:
Upvote 0
Change this...
If Not Intersect(Range("H2:H755"), Target) Is Nothing Then

To this...
If Not Intersect(Range("I:M"), Target) Is Nothing Then

Otherwise, I don't understand what you're asking.

I want this in the columns (I,J,K,L,M) but to put in diffrent data for diffrent columns with more the one to pick like in the first picture (column "H").

wBNwQsa
wBNwQsa


For every column I have diffrent data like in the second picture so i wanna be able to pick data for column "I" in the DropDown-sheet from column "I" in the DropDownData-sheet

f2axGbk


Thank you!
 
Upvote 0
I want this in the columns (I,J,K,L,M) but to put in diffrent data for diffrent columns with more the one to pick like in the first picture (column "H").

http://imgur.com/wBNwQsa

For every column I have diffrent data like in the second picture so i wanna be able to pick data for column "I" in the DropDown-sheet from column "I" in the DropDownData-sheet

http://imgur.com/f2axGbk

Thank you!
 
Upvote 0
Here is my solution to your problem:
http://www.vlsiip.com/exceltips.html
'enjoy
-Aviral Mittal

Is there anyway, I can create a drop down list that allows users to choose items from a list and place them into a single cell with comma separation (e.g. Rome, New York, Rio Di Janero)

If I could limit it to one item per cell, a simple drop down data validation list would work, but I need to limit entry to 1 cell/column.

Any suggestions would be much appreciated.
 
Upvote 0
Is there anyway, I can create a drop down list that allows users to choose items from a list and place them into a single cell with comma separation (e.g. Rome, New York, Rio Di Janero)

If I could limit it to one item per cell, a simple drop down data validation list would work, but I need to limit entry to 1 cell/column.

Any suggestions would be much appreciated.


download the complete solution from here:
http://www.vlsiip.com/exceltips.html

Hope it helps.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top