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]