Looping through ComboBoxes in a worksheet

[TonyM]

I have a number of worksheets with combobox lists in them.
What I want to do is populate the comboboxes with items
listed in named ranges on a master worksheet but am not
sure how I would loop through each combobox on each
worksheet.

The code for populating I already have. I need to look at
each combobox on each sheet and if combobox is cbOne then
populate with items from list1, cb2 - list2 etc.

Something along the lines of:-

Dim sht as worksheet, cb as comboBox

For each sht in ActiveWorkbook
For each cb in sht.Controls 'controls collection
_ 'will not work here
'add list items
Next
Next


Can anyone help ??

regards,

TonyM

 

[Tom Ogilvy]

dim cb as MsForms.Combobox
dim ole as OleObject
dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
for each ole in sh.OleObjects
if typeof ole.Object is MSForms.Combobox then
set cb = ole.Object
if cb.Name = "cb1" then

elseif cb.Name = "cb2" then

end if
end if
Next
Next

 

[Guest]

excellent!

thanks, Tom

-----Original Message-----
dim cb as MsForms.Combobox
dim ole as OleObject
dim sh as Worksheet
for each sh in ActiveWorkbook.Worksheets
for each ole in sh.OleObjects
if typeof ole.Object is MSForms.Combobox then
set cb = ole.Object
if cb.Name = "cb1" then

elseif cb.Name = "cb2" then

end if
end if
Next
Next

--
Regards,
Tom Ogilvy




.