Multiple Select from a drop down list

[shamjon]

Hi,

I was wondering if anyone could help me find a way of picking multiple instances from a drop-down list.

I've got a drop-down list that contain names. What I need to be able to do is select multiple names by by holding down the 'Ctrl' button (or any other appropriate method). Once the names have been selected, I need the cell to contain all these names, separated by a space and a comma.

Any help anyone can provide will be greatly appreciated!

Thanks you in advance.

ps: first time post long time user!

 

[shamjon]

You were right, had another go at putting the list box together and it correctly put a space and comma between the names selected.

My last question is how do I make the cell capture these names.

After I select the names they all do appear in the designated cell. But when I click on another cell (or away from the list) the names are deselected and the cell is empty.

How can I get the cell to permanently capture the selected item, so that you can still do work in the rest of the sheet.

Again thank you for being so helpful.

 

[Andrew Poulsom]

Sorry, that's my fault. The LostFocus event is changing the selections so the Change event is being called.

Try this:

Private Sub ListBox1_Change()
    Dim i As Integer
    Dim Msg As String
    If Disable = True Then Exit Sub
    For i = 0 To ListBox1.ListCount - 1
       If ListBox1.Selected(i) = True Then
          If Msg = "" Then
             Msg = ListBox1.List(i)
          Else
             Msg = Msg & ", " & ListBox1.List(i)
          End If
       End If
    Next i
    Range("A1").Value = Msg
End Sub

Private Sub ListBox1_LostFocus()
    Dim i As Integer
    Disable = True
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = False
    Next i
    Disable = False
End Sub

 

[shamjon]

Sorry mate, I've tried replacing the code with what you just pasted on your last post. But its still not capturing the selected names.

Could be it be anything within the properties of the list box.

Again your help is always welcomed

 

[Andrew Poulsom]

Is the MultiSelect property of the ListBox set to fmMultiSelectExtended?

 

[shamjon]

Cheers, I got around the problem by placing the List box in a userform. Which helped because now I don't have a huge list in the worksheet.

Just like to say thanks for all your help, and quick replies.

 

[spectraflame]

shamjon,

Can you explain more about a userform and how you got this code to function? I am looking for something that will do what you are doing with this code, but I am getting the same error that you were:

For i = 0 To ListBox4.ListCount - 1

Never Mind I figured out my problem.

Thanks,
Matthew