Deleting all dropdowns...

[Chip]

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?

 

[Tom Ogilvy]

ActiveSheet.DropDowns.Delete

 

[Ron de Bruin]

For all Shapes on your worksheet you can use this

ActiveSheet.DrawingObjects.Delete

 

[Ron de Bruin]

See Tom's reply

Much better for you

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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.

 

[Tom Ogilvy]

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.