VBA Excel objet indexed

[mor.mic]

I have a lot of objets in a sheet (for example combobox).
I have to load each combobox with the same datas.
How can I use a loop to do that ?
I tought to put the comboboxes in a collection but I don't know the syntaxe.
Can I use :
For each element in collection
Do ......
Next

Thanks for answering to a VBA excel beginner.

Michel MORICE

 

[Tom Ogilvy]

For combobox from the control toolbox toolbar

Dim oleObj as OleObject
for each oleObj in Activesheet.OleObjects then
if typeof oleObj.Object is MSForms.ComboBox
then oleObj.ListFillRange = "Sheet1!A1:A10"
end if
Next

from the forms toolbar

dim cbox as DropDown
for each cbox in Activesheet.DropDowns
cbox.ListfillRange = "sheet1!A1:A10"
Next

 

[mor.mic]

Thanks Tom
You answered exactly to my question.

For combobox from the control toolbox toolbar

Dim oleObj as OleObject
for each oleObj in Activesheet.OleObjects then
if typeof oleObj.Object is MSForms.ComboBox
then oleObj.ListFillRange = "Sheet1!A1:A10"
end if
Next

from the forms toolbar

dim cbox as DropDown
for each cbox in Activesheet.DropDowns
cbox.ListfillRange = "sheet1!A1:A10"
Next