Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping checkboxes embedded in worksheet | Excel Programming | |||
Looping down list and each time copying to another worksheet | Excel Programming | |||
Looping down list and each time copying to another worksheet | Excel Programming | |||
building a text string while looping though a worksheet | Excel Programming | |||
Control ComboBoxes & Worksheet Protection | Excel Programming |