Looping through ComboBoxes in a worksheet
 

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

Looping through ComboBoxes in a worksheet
 

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

"TonyM" wrote in message
...
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

Looping through ComboBoxes in a worksheet
 

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

"TonyM" wrote in
message
...
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


.