I need to delete 33 Comboxes all on the same sheet by using a macro. I am
using very simple code as below, which I got from recording a macro and
deleting one ComboBox which worked fine.

ActiveSheet.Shapes("ComboBox1").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox2").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox3").Select
Selection.Delete

And so on until I have deleted all 33. My problem is that I get an error
message stating:

Run-Time error '-2147024809 (80070057)
The item with the specified name wasn't found.

I have checked the properties of the boxes and they appear to be "named"
correctly. However, if I use On Error Resume Next all but two are deleted.

Any help and advice would be much appreciated as it's driving me "NUTS".

--
Kind Regards

Mick

Assuming comboboxes from the Control Toolbox Toolbar

Dim obj as OleObject
for each obj in Activesheet.OleObjects
if typeof obj.Object is MSForms.Combobox then
obj.Delete
end if
Next

--
Regards,
Tom Ogilvy

"Grandad" wrote in message
...
I need to delete 33 Comboxes all on the same sheet by using a macro. I am
using very simple code as below, which I got from recording a macro and
deleting one ComboBox which worked fine.

ActiveSheet.Shapes("ComboBox1").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox2").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox3").Select
Selection.Delete

And so on until I have deleted all 33. My problem is that I get an error
message stating:

Run-Time error '-2147024809 (80070057)
The item with the specified name wasn't found.

I have checked the properties of the boxes and they appear to be "named"
correctly. However, if I use On Error Resume Next all but two are
deleted.

Any help and advice would be much appreciated as it's driving me "NUTS".

--
Kind Regards

Mick

Tom

Thanks, unfortunately I get a run-time error '438': Object doesn't support
this property or method, when it gets to "for each obj in
Activesheet.OleObjects"

Thanks

Mick

"Tom Ogilvy" wrote in message
...
Assuming comboboxes from the Control Toolbox Toolbar

Dim obj as OleObject
for each obj in Activesheet.OleObjects
if typeof obj.Object is MSForms.Combobox then
obj.Delete
end if
Next

--
Regards,
Tom Ogilvy

"Grandad" wrote in message
...
I need to delete 33 Comboxes all on the same sheet by using a macro. I
am
using very simple code as below, which I got from recording a macro and
deleting one ComboBox which worked fine.

ActiveSheet.Shapes("ComboBox1").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox2").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox3").Select
Selection.Delete

And so on until I have deleted all 33. My problem is that I get an error
message stating:

Run-Time error '-2147024809 (80070057)
The item with the specified name wasn't found.

I have checked the properties of the boxes and they appear to be "named"
correctly. However, if I use On Error Resume Next all but two are
deleted.

Any help and advice would be much appreciated as it's driving me "NUTS".

--
Kind Regards

Mick

In a general module, this deleted all the comboboxes from the control
toolbox toolbar:

Sub Tester9()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
obj.Delete
End If
Next

End Sub

You can replace ActiveSheet with something Worksheets("Sheet1") if that is
where your comboboxes are located.

If you have dropdowns from the Forms toolbar

ActiveSheet.DropDowns.Delete

These are proven methods used successfully hundreds of times. If they don't
work for you, then you are probably in a sheet module or have some other
type of referencing problem.

--
Regards,
Tom Ogilvy



"Grandad" wrote in message
...
Tom

Thanks, unfortunately I get a run-time error '438': Object doesn't support
this property or method, when it gets to "for each obj in
Activesheet.OleObjects"

Thanks

Mick

"Tom Ogilvy" wrote in message
...
Assuming comboboxes from the Control Toolbox Toolbar

Dim obj as OleObject
for each obj in Activesheet.OleObjects
if typeof obj.Object is MSForms.Combobox then
obj.Delete
end if
Next

--
Regards,
Tom Ogilvy

"Grandad" wrote in message
...
I need to delete 33 Comboxes all on the same sheet by using a macro. I
am
using very simple code as below, which I got from recording a macro and
deleting one ComboBox which worked fine.

ActiveSheet.Shapes("ComboBox1").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox2").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox3").Select
Selection.Delete

And so on until I have deleted all 33. My problem is that I get an
error
message stating:

Run-Time error '-2147024809 (80070057)
The item with the specified name wasn't found.

I have checked the properties of the boxes and they appear to be
"named"
correctly. However, if I use On Error Resume Next all but two are
deleted.

Any help and advice would be much appreciated as it's driving me
"NUTS".

--
Kind Regards

Mick

Tom

Thanks for your help, it turned out that I had missed the "S" of the end of
OLEObjects

Regards

Mick

"Tom Ogilvy" wrote in message
...
In a general module, this deleted all the comboboxes from the control
toolbox toolbar:

Sub Tester9()
Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
obj.Delete
End If
Next

End Sub

You can replace ActiveSheet with something Worksheets("Sheet1") if that
is
where your comboboxes are located.

If you have dropdowns from the Forms toolbar

ActiveSheet.DropDowns.Delete

These are proven methods used successfully hundreds of times. If they
don't
work for you, then you are probably in a sheet module or have some other
type of referencing problem.

--
Regards,
Tom Ogilvy



"Grandad" wrote in message
...
Tom

Thanks, unfortunately I get a run-time error '438': Object doesn't
support
this property or method, when it gets to "for each obj in
Activesheet.OleObjects"

Thanks

Mick

"Tom Ogilvy" wrote in message
...
Assuming comboboxes from the Control Toolbox Toolbar

Dim obj as OleObject
for each obj in Activesheet.OleObjects
if typeof obj.Object is MSForms.Combobox then
obj.Delete
end if
Next

--
Regards,
Tom Ogilvy

"Grandad" wrote in message
...
I need to delete 33 Comboxes all on the same sheet by using a macro.
I
am
using very simple code as below, which I got from recording a macro
and
deleting one ComboBox which worked fine.

ActiveSheet.Shapes("ComboBox1").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox2").Select
Selection.Delete
ActiveSheet.Shapes("ComboBox3").Select
Selection.Delete

And so on until I have deleted all 33. My problem is that I get an
error
message stating:

Run-Time error '-2147024809 (80070057)
The item with the specified name wasn't found.

I have checked the properties of the boxes and they appear to be
"named"
correctly. However, if I use On Error Resume Next all but two are
deleted.

Any help and advice would be much appreciated as it's driving me
"NUTS".

--
Kind Regards

Mick