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!

 

[Andrew Poulsom]

Welcome to the Board.

You can't multiselect from a drop down (either from Data Validation or in a ComboBox from a Control ToolBox). But you can from a ListBox. See:

http://216.92.17.166/board2/viewtopic.php?t=60274

 

[shamjon]

I've had a chance to do a bt of reading on listboxes, but I still need some help with putting it into practice.

How do I get the list box to return the multiple entries into a specific cell, where each name is separated by a space and comma.

Wasn't sure how your private sub would work, so if you could clarify that it would be appreciated.

Cheers!

 

[Andrew Poulsom]

In my code I have:

For i = 0 To ListBox1.ListCount - 1 
   If ListBox1.Selected(i) = True Then 
      Msg = Msg & ListBox1.List(i) & vbCrLf 
   End If 
Next i

To get this separated by a comma and a space it would be:

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

Then instead of:

MsgBox "You selected: " & vbCrLf & Msg

you would need something like:

Range("A1").Value = Msg

 

[shamjon]

The macro goes into debug mode and stop at the part highlighted in red:

Private Sub Listbox76_Change()
Dim i As Integer
Dim Msg

Range("E9").Value = Msg

For i = 0 To ListBox76.ListCount - 1 If ListBox76.Selected(i) = True Then
If Msg = "" Then
Msg = ListBox76.List(i)
Else
Msg = Msg & ", " & ListBox76.List(i)
End If
End If
Next i

End Sub

any suggestions, please!

 

[Andrew Poulsom]

In the Control ToolBox click the Design icon (top left). Right click the ListBox and view code. You will get this:

Private Sub ListBox1_Click()

End Sub

which you can delete. Then paste this in:

Private Sub ListBox1_Change()
    Dim i As Integer
    Dim Msg As String
    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
    For i = 0 To ListBox1.ListCount - 1
        ListBox1.Selected(i) = False
    Next i
End Sub

Press Alt+F11 to return to your worksheet and click the Design icon again to exit design mode. The selections will clear when the user moves away from the ListBox.

Note that the MultiSelect property of the ListBox should be set to fmMultiSelectExtended.

 

[shamjon]

They code enters into break mode when i try to use the list box.

The code highlighted in red is where it stops.

Private Sub ListBox76_Change()
Dim i As Integer
Dim Msg As String
For i = 0 To ListBox76.ListCount - 1

If ListBox76.Selected(i) = True Then
If Msg = "" Then
Msg = ListBox76.List(i)
Else
Msg = Msg & ", " & ListBox76.List(i)
End If
End If
Next i
Range("e9").Value = Msg
End Sub

Private Sub ListBox76_LostFocus()
Dim i As Integer
For i = 0 To ListBox76.ListCount - 1
ListBox76.Selected(i) = False
Next i
End Sub

Thanks for continue help, today.

 

[Andrew Poulsom]

And the error is?

How many items in your ListBox? Maybe i needs to be declared as Long.

 

[shamjon]

When I try to select a name from the list there is a - 'Run time error '424' Object required.

It also highlights the code in red from my previous post.

There are currently 20 names in the list but this may be added to. If that makes a difference.

 

[Andrew Poulsom]

I don't know why that should happen unless there is a typo in your code. But assuming you pasted what you have into your message, it looks OK.