Deleting all dropdowns...
 

I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

ActiveSheet.DropDowns.Delete

--
Regards,
Tom Ogilvy


"Chip" wrote in message
oups.com...
I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

For all Shapes on your worksheet you can use this

ActiveSheet.DrawingObjects.Delete




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip" wrote in message oups.com...
I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

See Tom's reply

Much better for you

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
For all Shapes on your worksheet you can use this

ActiveSheet.DrawingObjects.Delete




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip" wrote in message oups.com...
I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

Here is some code that Dave Peterson and I came up with a while back. It
deletes the shapes, but not any code behind it

Sub testme()
Dim shp As Shape
Dim testStr As String
Dim OkToDelete As Boolean

For Each shp In ActiveSheet.Shapes
OkToDelete = True

testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If testStr = "" Then
'keep it
OkToDelete = False
End If
End If
End If

If OkToDelete Then
shp.Delete
End If

Next shp

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chip" wrote in message
oups.com...
I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

Just to add -
You only need to be this elaborate if you have Autofilter dropdowns on your
worksheet - I would think this would be unusual if you have forms control
dropdowns on the sheet. If you won't, then

Activesheet.Dropdowns.Delete

is sufficient.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Here is some code that Dave Peterson and I came up with a while back. It
deletes the shapes, but not any code behind it

Sub testme()
Dim shp As Shape
Dim testStr As String
Dim OkToDelete As Boolean

For Each shp In ActiveSheet.Shapes
OkToDelete = True

testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If testStr = "" Then
'keep it
OkToDelete = False
End If
End If
End If

If OkToDelete Then
shp.Delete
End If

Next shp

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chip" wrote in message
oups.com...
I want to be able to delete (using VBA) all the dropdowns on a
worksheet. When I record the act of deleting a dropdown I get:

ActiveSheet.Shapes("Drop Down 24").Select
Selection.Delete

How to I do it for more than Drop Down 24?

Deleting all dropdowns...
 

...
Just to add -
You only need to be this elaborate if you have Autofilter dropdowns on
your
worksheet -

or Data Validation.

And if you do, and you delete them, you can't get them back.

Deleting all dropdowns...
 

In Excel 2003, I couldn't reproduce the problem with a Data Validation
dropdown whether it was visible or not at the time the command was issued.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...

...
Just to add -
You only need to be this elaborate if you have Autofilter dropdowns on
your
worksheet -

or Data Validation.

And if you do, and you delete them, you can't get them back.