Find and Delete all Comboboxes in Excel Sheet

Question

I want to use VBA to delete all comboboxes in a sheet (form control type not active X)

I have

For Each s In ActiveSheet.Shapes
s.Delete
Next s

The problem is it deletes all my shapes, and I'm having trouble finding the logic just to delete comboboxes (which have different names)

Thanks for any help!

score 8 · Accepted Answer · answered Jun 16 '14 at 21:25

8

With ActiveSheet.DropDowns
Do While .Count > 0
    .Item(1).Delete
Loop
End With

or just

activesheet.dropdowns.delete

answered Jun 16 '14 at 21:25

Tim Williams

154,628
8
97
125

Never heard of an object `dropdowns`, does it still exist on a sheet in 2020? [Here](https://stackoverflow.com/a/3832788/1705829) is another usecase which does not work. – Timo Aug 25 '20 at 12:33
1

@Timo - yes, still works in 2020... If you're having a problem with some code maybe worth posting a question about it? – Tim Williams Aug 25 '20 at 16:00
Tim, it does not delete the ActiveX Elements, just the "normal" ones, which is what the questioner wanted. I however want to `clear` all dropdowns in a loop, not delete. BTW I use Edge and when I put a `@` at the beginning to address you, the `@Tim` gets deleted. Ok, seen [this](https://meta.stackexchange.com/questions/43019/how-do-comment-replies-work) – Timo Aug 26 '20 at 07:09

score 0 · Answer 2 · answered Jun 16 '14 at 21:30

0

If the only Forms you have are these comboboxes, then:

Sub qwerty()
    Dim s As Shape
    For Each s In ActiveSheet.Shapes
        If s.Type = 8 Then
            s.Delete
        End If
    Next s
End Sub

answered Jun 16 '14 at 21:30

Gary's Student

95,722
10
59
99

I get index out of range. – Timo Aug 25 '20 at 12:27
the `s.type` is `msoformcontrol` which is 8 as of [here](https://learn.microsoft.com/en-us/office/vba/api/office.msoshapetype) – Timo Aug 25 '20 at 12:36

Find and Delete all Comboboxes in Excel Sheet

2 Answers2

Linked