ActiveX combo box

[Carla]

I'm trying to programmatically create ActiveX combo boxes
(not the Microsoft Forms combo box). I am able to create
the combo boxes, however, I don't know how to refer to
them once they are created. I use the following code to
create them:

-----------------------------------------------------------
Function AddDropDown(ddRange As Range, rptMapSheet As
String) As Boolean

Dim ddbox As OLEObject
Dim YN As Range
Dim i, j

Set YN = Sheets(RPT_MAP_SHEET).Range(CELL_FORMAT_MAP)

For i = 1 To ddRange.Cells.Count
With ddRange(i)
Set ddbox = Sheets(rptMapSheet).OLEObjects.Add
(ClassType:="Forms.ComboBox.1", Link:=False,
DisplayAsIcon:=False, Left:=.Left, Top:=.Top,
Width:=.Width, Height:=.Height)
End With

With ddbox
.ListFillRange = YN.Name
.LinkedCell = ddRange(i).Address
.Placement = xlMoveAndSize
End With

Next i

AddDropDown = True
End Function
-----------------------------------------------------------

This function will create ComboBox1, ComboBox2, etc.,
depending on the number of cells in range "ddRange".
However, I don't know how to access them in order to
update the properties of each combo box. As far as I can
tell, the only way to update certain properties, like
BoundColumn or ColumnCount, is to explicitly reference the
combo box object. In otherwords, I'd have to code:

Sheets(rptMapSheet).ComboBox1.BoundColumn = 1

My problem is this:
If I'm looping through a range of a varying amount of
cells, how am I to know at runtime how many ComboBox
statements to run? I can't loop, because I don't know how
to variably refer to the combo box object within the sheet
because there is no ComboBoxes collection.

I hope I've explained this well enough. If so, please clue
me in on how I can code this. Thanx.

 

[Tom Ogilvy]

ddbox.object is the combobox itself.

With ddbox
.ListFillRange = YN.Name
.LinkedCell = ddRange(i).Address
.Placement = xlMoveAndSize
.Object.BoundColumn = 1
End With

 

[Carla]

THANK YOU! THANK YOU! THANK YOU!

Gee, you're smart.