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.com bobox.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

Hi
You could try something like this

Dim cb As Shape
On Error Resume Next
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.Delete
End If
Next

;-)

Martin Walke wrote in message
...
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.com bobox.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

Thanks Loomah for the quick reply.

I have tried exactly that (almost) but the problem is that the
design-time-added combo has the *same* name as the first dynamically added
one. It seems bizarre to have two controls with the same name. Is there any
other way of distinguishing between two controls, particularly ones that
have been added at design time and ones that have been added dynamically?

BTW, why's the On error statement there?

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Hi
You could try something like this

Dim cb As Shape
On Error Resume Next
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.Delete
End If
Next

;-)

Martin Walke wrote in message
...
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.com bobox.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

Martin
I don't know of any way to distinguish between design & run time created
controls - that doesn't mean there isn't a way!
There's probably some reason for this but why can't you change the name of
the control(s) created at design time to allow the code to work with the
default names of those created at run time?

The On Error statement is (was) there as I was having problems stepping thru
the code. It isn't necessary - apart from the fact that there should always
be some sort of error handling in code.

Martin Walke wrote in message
...
Thanks Loomah for the quick reply.

I have tried exactly that (almost) but the problem is that the
design-time-added combo has the *same* name as the first dynamically added
one. It seems bizarre to have two controls with the same name. Is there
any
other way of distinguishing between two controls, particularly ones that
have been added at design time and ones that have been added dynamically?

BTW, why's the On error statement there?

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Hi
You could try something like this

Dim cb As Shape
On Error Resume Next
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.Delete
End If
Next

;-)

Martin Walke wrote in message
...
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.com bobox.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

You misunderstand me (or I haven't made myself clear :-).

The design time control is named (via the properties), cbxCategories. The
dynamic ones, via the Add method, are named ComboBox1, ComboBox2 etc.

However, when I reference them at the runtime, the cbxCategories reports
that its name is ComboBox1 (!) and that the first dynamic one is *also*
ComboBox1 so if I then try and delete the control named ComboBox1 it deletes
the wrong one!

But there appears to be light at the end of the tunnel! I was using
cb.object.name to name the control but have since rationalised my code with
functions etc and can now use cb.name which the control appears to 'know'
and understand.

Let me experiment a bit more.

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Martin
I don't know of any way to distinguish between design & run time created
controls - that doesn't mean there isn't a way!
There's probably some reason for this but why can't you change the name of
the control(s) created at design time to allow the code to work with the
default names of those created at run time?

The On Error statement is (was) there as I was having problems stepping
thru
the code. It isn't necessary - apart from the fact that there should
always
be some sort of error handling in code.

Martin Walke wrote in message
...
Thanks Loomah for the quick reply.

I have tried exactly that (almost) but the problem is that the
design-time-added combo has the *same* name as the first dynamically
added
one. It seems bizarre to have two controls with the same name. Is there
any
other way of distinguishing between two controls, particularly ones that
have been added at design time and ones that have been added
dynamically?

BTW, why's the On error statement there?

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Hi
You could try something like this

Dim cb As Shape
On Error Resume Next
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.Delete
End If
Next

;-)

Martin Walke wrote in message
...
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.com bobox.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

Loomah,

It's sorted!! I'm not 100% sure what happened to make things right but
having now been able to name the controls to something totally different and
name them in a different part of code seems to have sorted it out.

It may have been the way I was referencing the controls as objects. I do
sometimes get very confused about which/what/where although it seems simple
enough when you stand back!!

Thanks again for being a sounding board if nothing else

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Martin
I don't know of any way to distinguish between design & run time created
controls - that doesn't mean there isn't a way!
There's probably some reason for this but why can't you change the name of
the control(s) created at design time to allow the code to work with the
default names of those created at run time?

The On Error statement is (was) there as I was having problems stepping
thru
the code. It isn't necessary - apart from the fact that there should
always
be some sort of error handling in code.

Martin Walke wrote in message
...
Thanks Loomah for the quick reply.

I have tried exactly that (almost) but the problem is that the
design-time-added combo has the *same* name as the first dynamically
added
one. It seems bizarre to have two controls with the same name. Is there
any
other way of distinguishing between two controls, particularly ones that
have been added at design time and ones that have been added
dynamically?

BTW, why's the On error statement there?

Martin

"Loomah" <bellm AT globalnet dot co dot uk wrote in message
...
Hi
You could try something like this

Dim cb As Shape
On Error Resume Next
For Each cb In ActiveSheet.Shapes
If cb.Name Like "ComboBox*" Then
cb.Delete
End If
Next

;-)

Martin Walke wrote in message
...
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.com bobox.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