View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Adding Items To Active X ComboBox on Sheet

for each item in Mylist
Activesheet.Combobox1.AddItem item
Next

or

assume the OleObject name of the combobox is MyCombobox (in xl2000 and
later, the name of the combobox and the oleobject name should be the same.

for each item in List
Activesheet.OleObjects("MyComboBox").Object.AddIte m Item
Next


Here is a tested Example:

Sub AddStuff()
Dim rng as Range
Dim cell as Range
With Worksheets("Sheet8")

Set rng = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xlDown))
For Each cell In rng
.OLEObjects("MyComboBox").Object _
.AddItem cell.Value
Next
End With
End Sub

--
Regards,
Tom Ogilvy


Dan Gesshel wrote in message
...
Hello.

I am trying to add a list to an ActiveX ComboBox on the sheet itself. I am
able to do this using a Userform:

For Each Item In MyList
MyForm.MyComboBox.AddItem Item
Next Item

This works great... for Userforms. I'm having trouble with the correct
syntax for a ComboBox residing on the sheet, and not in a Userform. Can
anyone out there help?

Thanks.

Dan