VBA Excel objet indexed
 

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

VBA Excel objet indexed
 

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


--
Regards,
Tom Ogilvy



mor.mic wrote in message
...
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

VBA Excel objet indexed
 

Thanks Tom
You answered exactly to my question.

"Tom Ogilvy" a écrit dans le message news:
...
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


--
Regards,
Tom Ogilvy



mor.mic wrote in message
...
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