Delete ComboBoxes using a macro?
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
|