ActiveX combo box
 

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.

ActiveX combo box
 

ddbox.object is the combobox itself.

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


--
Regards,
Tom Ogilvy

"Carla" wrote in message
...
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.

ActiveX combo box
 

THANK YOU! THANK YOU! THANK YOU!

Gee, you're smart. :D

-----Original Message-----
ddbox.object is the combobox itself.

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


--
Regards,
Tom Ogilvy

"Carla" wrote in message
...
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.




.