Drop Down- Multiple Selections

[nldstorm]

I am trying to create a drop down menu whereby a user can select multiple values, which are then placed into the same cell using a coma. For example, the drop down list would contain companies 1001, 1002, 1003 and 1004 (actually this drop down list would have about 200+ companies). I would like to have an option so that the user can select, for example, companies 1002 and 1004. The cell result should then be 1002, 1004. I have code that allows me to do this however each value must be selected serperatly causing the user to go back into the drop down list. I would like to have the user select all the companies at one time.

Hope you can help.

Thanks

 

[crimson_b1ade]

you should be using a ListBox (from the control toolbox) rather than a combobox. Listboxes allow for multiple selections. Below is some sample code that you can paste to your ListBox that will put in the selections into Cell B1. In addition, you'll have to update the following properties of the Listbox in design mode:

ListFillRange: (put in here the range where you companies are listed) i.e. A1:A200

MultiSelect: 1-fmMultiSelectMulti

Private Sub ListBox1_Change()
 
Dim myString As String

For i = 0 To ListBox1.ListCount - 1
    
    If ListBox1.Selected(i) = True Then
        myString = myString & ", " & ListBox1.List(i)
    End If
Next
    
Range("B1") = Mid(myString, 3)
 
End Sub

 

[nldstorm]

Thanks a lot for the quick follow up. Is there any way you can guide me a bit more how to create the box and input the code? As you can see from this request I am a beginner with all of this.

Thanks again for your help.

 

[crimson_b1ade]

1. from the menu bar: View\Toolbars\Control Toolbox
2. Select the 'List box' icon from the Control Toolbox
3. Drag your cursor to where your list box is to be located on your sheet
4. right-click the listbox, select 'view code' (paste the code I provided above)
5. right-click the listbox, select properties:
a) ListFillRange: (put in here the range where you companies are listed) i.e. A1:A200

b)MultiSelect: 1-fmMultiSelectMulti

6. select the design mode icon on the Control toolbox to exit design mode.

7. test the listbox to make sure it works the way you would like.

 

[nldstorm]

Thanks. I think that ive got it working. But am I able to use the selected values in pivots and filters?

 

[crimson_b1ade]

now i'm confused. what was the purpose of your original post? do you just want to filter a table based on a selection? what is your ultimate goal?

 

[nldstorm]

Sorry for the confusion. What I want to do is the following.

I am trying to create a spreadsheet with a listing of values and where this value appears in companies. Once this spreadsheet is compiled I would like to use filters to determine where each value comes up. Ive created a sample spreadsheet which can be found at http://s02.eeestorage.com/simpledownload/?sharecode=1e6c6197-0d17-496a-8ba9-562c885c7ed9

Thanks