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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
You could create a UserForm that has a multi-selects listbox. The UserForm could be triggered when the user select a specific cell or clicks a button.

This code will concatenate multiple selections from a Data Validation dropdown list (code source) in cell M10.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim oldVal As String
    Dim newVal As String
    
    If Target.Address(0, 0) <> "[COLOR="Red"]M10[/COLOR]" Then Exit Sub

    On Error GoTo ReEnable
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal

    If oldVal <> "" And newVal <> "" Then
        Target.Value = oldVal & ", " & newVal
    End If
ReEnable:
    Application.EnableEvents = True
    
End Sub
 
Upvote 0
Thanks AF -

My VBA experience is nil but I found an explanation of how to create a userform so I've got a few questions.

Is M11 the cell where the data is entered or where the list starts?

I need to be able to enter the data in a number of specific cells D15:D314- any suggestions for how I could trigger the same userform when the user clicks on anyone of those 300 cells? The data for the checklist is in CL4:CL103

Also - any suggestions on other resources to see how to create the checklist?
 
Upvote 0
Thanks AF -

My VBA experience is nil but I found an explanation of how to create a userform so I've got a few questions.

Is M11 the cell where the data is entered or where the list starts?

I need to be able to enter the data in a number of specific cells D15:D314- any suggestions for how I could trigger the same userform when the user clicks on anyone of those 300 cells? The data for the checklist is in CL4:CL103

Also - any suggestions on other resources to see how to create the checklist?

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]
 
Upvote 0
Alpha Frog -

Brilliant - Thanks so much for walking me through that - works perfectly!

A couple of questions for refinement:

My cities list is imported into the worksheet from another source and may or may not contain blanks, do you have any suggestions for how to modify the code to ignore blanks?

Also - Is it possible to skip the import of the data into the worksheet and for the Userform to just reference a named range in the Cities.xls worksheet?

Thanks Again!
 
Upvote 0
AF -

Strange but second time through and the pop-up isn't activating when I click on the cells. The exact code you gave is in the places you suggested and Macros are enabled - is there anything else I could be missing?
 
Upvote 0
My cities list is imported into the worksheet from another source and may or may not contain blanks, do you have any suggestions for how to modify the code to ignore blanks?

Also - Is it possible to skip the import of the data into the worksheet and for the Userform to just reference a named range in the Cities.xls worksheet?

Ignore Blanks - easy

Where in Cities.xls is the cities data located; File path, worksheet name, and cell range?

...second time through and the pop-up isn't activating when I click on the cells.
Run this macro below and then click on a cell in D15:D314. If it works again, it means you have some code somewhere that is disabling the event triggers. Look for a line of code like this; Application.EnableEvents = False

Code:
Sub ReEnable()
    Application.EnableEvents = True
End Sub
 
Upvote 0
The file is located in the same path and directory as the worksheet with the VBA which is C:\Users\Mike\Documents\SCC\Next Draft\[Cities.xlsm]

The Worksheet is named Sheet1 and the range (A4:A253) is named Cities.

However, the path needs to be relative to the VBA workbook if possible since I'm going to need to deploy this to several computers with different paths and can't change the path each time.

Thanks
 
Upvote 0
Userform's module:
Code:
[color=darkblue]Const[/color] MySeperator [color=darkblue]As[/color] [color=darkblue]String[/color] = ", "

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
    
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color], strPath [color=darkblue]As[/color] [color=darkblue]String[/color], strFile [color=darkblue]As[/color] [color=darkblue]String[/color], wb [color=darkblue]As[/color] Workbook
    
    strPath = ThisWorkbook.Path & Application.PathSeparator [color=green]' File path[/color]
    strFile = "Cities.xls"                                  [color=green]' File name[/color]
    
    [color=darkblue]If[/color] Len(Dir(strPath & strFile)) [color=darkblue]Then[/color]
    
        Application.ScreenUpdating = [color=darkblue]False[/color]
        [color=darkblue]Set[/color] wb = Workbooks.Open(Filename:=strPath & strFile, ReadOnly:=True)
        [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
            vCities = wb.Sheets("Sheet1").Range("A4:A253")  [color=green]'Cities List: worksheet and range[/color]
            wb.Close SaveChanges:=[color=darkblue]False[/color]
        On [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        Application.ScreenUpdating = [color=darkblue]True[/color]
    
        [color=darkblue]With[/color] ListBox1
            [color=green]'Filter Blanks[/color]
            [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](vCities) [color=darkblue]To[/color] [color=darkblue]UBound[/color](vCities)
                [color=darkblue]If[/color] vCities(i, 1) <> "" [color=darkblue]Then[/color] .AddItem Trim(vCities(i, 1))
            [color=darkblue]Next[/color] i
            .MultiSelect = fmMultiSelectMulti
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        UserForm1.Caption = "Select Cities"
        CommandButton1.Caption = "Okay"
            
    [color=darkblue]Else[/color]
        MsgBox "Cannot locate file: " & vbCr & vbCr & strPath & strFile, , "File Not Found"
        Unload Me
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[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=green]'Read selections[/color]
        [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) & MySeperator
                .Selected(i) = [color=darkblue]False[/color]    [color=green]' Clear selections[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [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) - Len(MySeperator))
        ActiveCell.Value = strTemp
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    UserForm1.Hide
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Activate()
    [color=green]'Match Listbox selections with ActiveCell selections[/color]
    [color=darkblue]Dim[/color] strCell [color=darkblue]As[/color] [color=darkblue]String[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]If[/color] Len(ActiveCell.Value) [color=darkblue]Then[/color]
        strCell = ActiveCell.Value & MySeperator
        [color=darkblue]With[/color] ListBox1
            [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] .ListCount - 1
                .Selected(i) = InStr(1, strCell, .List(i), 1)
            [color=darkblue]Next[/color] i
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_QueryClose(Cancel [color=darkblue]As[/color] [color=darkblue]Integer[/color], CloseMode [color=darkblue]As[/color] [color=darkblue]Integer[/color])
    [color=green]'Hide userform if Close "X" clicked[/color]
    [color=darkblue]If[/color] CloseMode = 0 [color=darkblue]Then[/color]
        Cancel = [color=darkblue]True[/color]
        Me.Hide
    [color=darkblue]End[/color] [color=darkblue]If[/color]
     
End [color=darkblue]Sub[/color]

Worksheet module:
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]
            [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
                UserForm1.Show
            [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] 0
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
 
Upvote 0
That is awesome Alpha Frog! Thanks so much.

One minor thing - If you try to make a change, you can select an additional city or deselect a previously selected city, but if you try to deselect all of the cities that were previously selected (even of only 1 was previously selected), it makes no changes to the selections.

What would it take to allow all to be cleared?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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