Populate from Combo Box

bksmith101 · Jul 7th 2004

I am trying to populate multiple cells from a combo box. I would like one of the options in the combo box to be a "list all". When this is chosen, I would like Excel to list in other cells all values contained in the combo box.

Any help??

Thanks

jmhans · Jul 7th 2004

Suppose your combobox is called ComboBox1 and you have a command button to enter the data. Suppose you wanted to put these values in column A of "Sheet1"

In your UserForm Module, you should have:

Code

Private Sub CommandButton1_Click()
If ComboBox1.Value = "List All" Then
For k = 0 To ComboBox1.ListCount - 1
    If k <> ComboBox1.ListIndex Then
    FindNewSpace(Range("A1")) = ComboBox1.List(k)
    End If
Next
End If
End Sub


Function FindNewSpace(startspot As Range) As Range
Line1:
If startspot = "" Then
Set FindNewSpace = startspot
GoTo Line2
Else
Set startspot = startspot.Offset(1, 0)
GoTo Line1
End If
Line2:


End Function

Display More

Of course, you can modify code to suit for Range, Control names, etc...
Let me know if you need help with that!

Hope that helps!

jmhans

bksmith101 · Jul 7th 2004

This is EXACTLY what I was trying to do!!!

Thanks for the help!!

bksmith101 · Jul 7th 2004

How do I clear the list after another selection is chosen?

Thanks

bksmith101 · Jul 7th 2004

Duh!! Never mind. Thanks for the help!

Populate from Combo Box

Participate now!

Share