Delete button control using VBA (2002/SP3)

Topic

New Reply

I get an error in line 4 of this code. What’s wrong with this code?

Public Sub DeleteButtonControl()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.FormControlType = xlButtonControl Then ‘ << Error occurs here: Run-time error '1004'
shp.Delete
End If
Next shp
End Sub

Reply | Quote

Replies

FormControlType is only valid for Form controls, so you have to check first whether the shape is a Form control.
Also, when deleting items from a collection, it’s best to loop backwards.

Public Sub DeleteButtonControl()
Dim i As Integer
For i = ActiveSheet.Shapes.Count To 1 Step -1
If ActiveSheet.Shapes(i).Type = msoFormControl Then
If ActiveSheet.Shapes(i).FormControlType = xlButtonControl Then
ActiveSheet.Shapes(i).Delete
End If
End If
Next i
End Sub

Reply | Quote

Thanks alot Hans. Works great.

Can you explain to me though why the original code with the statement “If shp.FormControlType = xlButtonControl Then” didn’t work?
Also, you wrote in your response that “when deleting items from a collection, it’s best to loop backwards.” Why is that?

Reply | Quote

The line

If shp.FormControlType = xlButtonControl Then

fails if the shape is not a forms control. The FormControlType property is only valid for forms controls, not for all shapes. So we first check the Type property to see whether the shape is a forms control, and only if so, check the FormControlType property.

About the loop: let’s look at a very simple example: the sheet contains three shapes of which the second one is a control button.

The code using For Each shp In ActiveSheet.Shapes will look at the first shape, skip it because it isn’t a button.
It then moves on to the second shape and deletes it because it is a button.
It then tries to look at the third shape, but there is no third shape any more: because we deleted the second shape, the third one has by now become the second one.
So you get an error message.

The code using For i = ActiveSheet.Shapes.Count To 1 Step -1 starts by looking at the third shape, and skips it.
It then moves on to the second shape and deletes it.
Finally, it looks at the first shape (which is still there) and skips it.

Note: some collections handle this problem gracefully if you use For Each, but most of them don’t. So it’s safest to loop backwards.

Reply | Quote

Great explanation.

One more question. The forms control is the one that you get from the “Forms” toolbar, not the “Control Toolbox” toolbar right?

Reply | Quote

Yes, that is correct.

Reply | Quote

And if you are using buttons from the Forms toolbar, you can loop the Buttons collection directly.
FWIW.

Reply | Quote