1

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.

enter image description here

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?

mvidude
  • 41
  • 7
  • I am afraid that such a button named "CommandButton1" does not exist on the active sheet... Did you try `ActiveSheet.Shapes("CommandButton3").Delete`? And you should be sure that the active sheet is the appropriate one... – FaneDuru Feb 01 '21 at 08:53
  • Note that in Excel there exist two different type of controls/buttons. FormControls and ActiveX Controls. Both work completely different, make sure you know the difference and how to use them. Where evere possible I recommend to stay far away from ActiveX and choose FormControls as ActiveX is known to be the source of many strange effects and bugs. – Pᴇʜ Feb 01 '21 at 09:07
  • @FaneDuru Yes I did. It does exists => https://i.imgur.com/lFUxH5u.png – mvidude Feb 01 '21 at 09:21
  • @Pᴇʜ I know. Fortunately, I have yet to experience (see or have) such a strange effect or bug myself. Until now, all has been good with ActiveX controls. – mvidude Feb 01 '21 at 09:23
  • @FaneDuru When I run that command, it's the same error, "Element with that name not found.". – mvidude Feb 01 '21 at 09:24
  • @FaneDuru If I create a new sheet add an ActiveX button it is named `CommandButton1` automatically and gets deleted with `ActiveSheet.Shapes("CommandButton1").Delete`. No issues. I cannot reproduce the issue. Still I recommend stay away from ActiveX. – Pᴇʜ Feb 01 '21 at 09:38
  • 2
    `For Each obj In ActiveSheet.Shapes: Debug.Print obj.OLEFormat.Object.Name: Next obj` What all names do you get? Did yo uname the button correctly using `newButton.Name = "WhatEverName"` – Siddharth Rout Feb 01 '21 at 09:43
  • Btw, `obj.Name` seems to return the caption name as does `obj.OLEFormat.Object.Name`! – shrivallabha.redij Feb 01 '21 at 10:06
  • @SiddharthRout I got "Object 39", "Object 40" and "Object 41" (minus the quotes, ofc). Huh. – mvidude Feb 01 '21 at 10:25
  • @SiddharthRout Yessss. That did it! I just used the `InStr` function with a test for "Object" and then deleted those. Please could post your comment/train of thought as a reply, I can then mark it as solved. – mvidude Feb 01 '21 at 10:27
  • No that is ok. Feel free to post yours and mark it as an answer. :) – Siddharth Rout Feb 01 '21 at 11:46

1 Answers1

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.

mvidude
  • 41
  • 7