Loop through ComboBoxes

[J-Man]

Is there a way to loop through ComboBoxes without knowing
the names of the comboboxes? Maybe something like...

For i = 1 To 10
ComboBox(i).AddItem(i)
Next i

[Vasant Nanavati]

Hi J-Man:

An example, which you can modify to your liking, of how to cycle through the
ComboBoxes on a UserForm:

Dim ctl As Control, i As Integer
For Each ctl In Me.Controls
If TypeOf ctl Is msforms.ComboBox Then
i = i + 1
ctl.AddItem i
End If
Next
--
Regards,

Vasant.

"J-Man" <j2c...@attbi.com> wrote in message
news:0e4501c17f81$1d4e3d20$b1e62ecf@tkmsftngxa04...

[J-Man]

I forgot to mention that I am not using a UserForm...Just
pulled the ComboBoxes from the Controls Toolbox.

>.
>

[Chip Pearson]

Try something like

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
Debug.Print OLEObj.Name
OLEObj.Object.AddItem "AAAA"
End If
Next OLEObj

The OLEObject object is sort of a container for all OLE Controls. The Object
property of OLEObject gets you a reference to the actual control contained
within OLEObject. You get and let properties of the actual control by going
through the Object property.

You can't use AddItem if the comboboxes have a ListFillRange property set to a
range.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"J-Man" <j2c...@attbi.com> wrote in message

news:13d301c17f8f$e0d4d520$39ef2ecf@TKMSFTNGXA08...

[Vasant Nanavati]

Hi J-Man:

Slightly different (and completely unintuitive) terminology, then:

Dim Obj As OLEObject, i As Integer
For Each Obj In Sheet1.OLEObjects
If TypeOf Obj.Object Is ComboBox Then

i = i + 1

Obj.Object.AddItem i

End If
Next
--
Regards,

Vasant.

"J-Man" <j2c...@attbi.com> wrote in message
news:13d301c17f8f$e0d4d520$39ef2ecf@TKMSFTNGXA08...