Multiple Select from a drop down list

shamjon

New Member
Joined
Feb 4, 2004
Messages
16
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!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

shamjon

New Member
Joined
Feb 4, 2004
Messages
16
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In my code I have:

Code:
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:

Code:
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:

Code:
MsgBox "You selected: " & vbCrLf & Msg

you would need something like:

Code:
Range("A1").Value = Msg
 

shamjon

New Member
Joined
Feb 4, 2004
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In the Control ToolBox click the Design icon (top left). Right click the ListBox and view code. You will get this:

Code:
Private Sub ListBox1_Click()

End Sub

which you can delete. Then paste this in:

Code:
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

New Member
Joined
Feb 4, 2004
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
And the error is?

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

shamjon

New Member
Joined
Feb 4, 2004
Messages
16
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top