MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Cake - ComboBox VBA create and fill

Posted by Brian P on April 17, 2001 1:03 PM

Maybe I'm tired!

I'm trying to create a vb code to create a ComboBox. I can create the ComboBox but am having trouble filling it. I want to fill it with data in a range name. Thanks.

Posted by Malc on April 17, 2001 1:15 PM

Select the combo box right click and select properties then select row source and enter your range name in there. The output of the combo box goes in control source.

Also have an experiment with additem type it in, select it and press F1 for more info. I find this a better way but is more complicated.

Posted by Brian P on April 17, 2001 1:55 PM

Thanks and heres what I ended up with


Thanks very much. I was oh so close!!!
Here's what I ended up with. Does excel 97 have the row source? I couldn't find it - but you helped me figure out what I needed. Thanks

Sub test()
Set RangeNameList = Range("RangeNameList").Cells
With Worksheets(1)
Set MyDropDown = .Shapes.AddFormControl(xlDropDown, 10, 10, 100, 15)
For Each cell In RangeNameList
MyDropDown.ControlFormat.AddItem cell.Value
Next cell
End With
End Sub

Posted by Malc on April 17, 2001 2:20 PM

Re: Thanks and heres what I ended up with

Well done. Excel 97 does have a rowsource property in the properties box it's hard to find though. Try selecting by alphabetical rather than category. Anyway your code works. You should add MyDropDown.Rowsource ="" before For Each... as that will clear out any items before loading the new ones otherwise it'll keep appending to anything that already in there.

Posted by Amroo on April 18, 2001 1:50 AM

creation code combo

Bonjour Brian P for De Palma?,
I want to know how to code to create a combobox, is it on a sheet or a userform?
2001 regards