VBA Excel objet indexed

  • Thread starter Thread starter mor.mic
  • Start date Start date
M

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
 
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
 
Thanks Tom
You answered exactly to my question.

Tom Ogilvy said:
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
Click to expand...
 
Back
Top