How can I delete an ActiveX Button with VBA by its name?

Question

I cannot figure out why this code does not "detect" the activex button (created with Siddarth's help in here: How to rename a newly created ActiveX button?). I see the button in front of me and its properties window looks like this.

I even tried to delete just one button with ActiveSheet.Shapes("CommandButton1").Delete, but that line results in an error window with a text saying something like "An item with that name wasn't found.". => Apparently I am not using the right name to address the button.

The code I am using is based on used Gareth's code in Word vba delete button not working

I added an if-condition to exclude "good" buttons with names set in snake case (hence "_"), i. e. the names were defined by me.

Sub del_button()
Dim obj As Object
For Each obj In ActiveSheet.Shapes
    If InStr(obj.OLEFormat.Object.Name, "CommandButton") > 0 And InStr(obj.OLEFormat.Object.Name, "_") < 1 Then
        'MsgBox (obj.OLEFormat.Object.Name) - just was there for me to "notice", so I am set to notice when a button is going to be deleted
        obj.Delete
    End If
Next obj
'ActiveSheet.Shapes("CommandButton1").Delete   ---- THIS LINE throws an error
End Sub

=> What name should I specify in the code then, if the one I used isn't correct? Or do I have to use something else entirely?

Answer 1

This was the code that worked:

Sub del_button()
Dim obj As Object
For Each obj In ActiveSheet.Shapes
    If InStr(obj.OLEFormat.Object.Name, "Object") > 0 And _
    InStr(obj.OLEFormat.Object.Name, "_") < 1 Then
        obj.Delete
    End If
Next obj
End Sub

Siddarth's comment included the helpful detail which was essentially the solution to my problem.

Microsoft's help page on shape objects also has some interesting bits on this topic, namely the following:

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.

You use the code name of a control in the names of its event procedures. However, when you return a control from the Shapes or OLEObjects collection for a sheet, you must use the shape name, not the code name, to refer to the control by name.

So with the 2 in mind, Some bits:

• The name, or rather, string, I had to check for was the shape name. And the shape name is visible in the name box.

• The shape name isn't localized, although its displayed text in the name box is. Hence the English term "Object" in the VBA code above.