[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]