M
Martin Walke
Hi,
I wonder if someone can point me in the right direction. I need to create a
variable number of combo boxes dynamically in a spread sheet, taking their
contents from sets of other spreadsheets.
Set cb = ActiveWorkbook.Sheets(1).OLEObjects.Add("Forms.combobox.1",
Left:=c.Left, Top:=c.Top, Height:=c.Height, Width:=c.Width)
(where c is the cell)
That part is fine, the first time the combo boxes are created.
The user selects from another combo box (design time added) to select
another set of spreadsheets to use to populate the dynamic ones.
The problem is when I want to re-create the page, I need to first delete the
current combo boxes before I create the new ones. How do I reference the
existing combo boxes? I can't add a Name reference to the Add method and I
can't seem to define the name after the combo box has been created (cb.name
= "ComboList1") so end up with the default one of combobox1, combobox2 etc.
If I simply loop through the Shapes collection (for each sh in Shapes), then
I don't seem to be able to distinguish between the design-time-added combo
and the dynamic ones even though they have markedly different names.
(cbxCategories vs comboboxn)
TIA
Martin
I wonder if someone can point me in the right direction. I need to create a
variable number of combo boxes dynamically in a spread sheet, taking their
contents from sets of other spreadsheets.
Set cb = ActiveWorkbook.Sheets(1).OLEObjects.Add("Forms.combobox.1",
Left:=c.Left, Top:=c.Top, Height:=c.Height, Width:=c.Width)
(where c is the cell)
That part is fine, the first time the combo boxes are created.
The user selects from another combo box (design time added) to select
another set of spreadsheets to use to populate the dynamic ones.
The problem is when I want to re-create the page, I need to first delete the
current combo boxes before I create the new ones. How do I reference the
existing combo boxes? I can't add a Name reference to the Add method and I
can't seem to define the name after the combo box has been created (cb.name
= "ComboList1") so end up with the default one of combobox1, combobox2 etc.
If I simply loop through the Shapes collection (for each sh in Shapes), then
I don't seem to be able to distinguish between the design-time-added combo
and the dynamic ones even though they have markedly different names.
(cbxCategories vs comboboxn)
TIA
Martin