Need to make a button that deletes itself

[CDienst]

Hello,

I'm looking to make a button that, as part of the macro it runs, deletes itself.
This is what I have so far

 ActiveSheet.OLEObjects("Button 1").Delete
    Application.Calculate
    Range("TimeSheet[Date(s)]").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

It all works fine, but with the addition of the first line to make the button delete itself, it errors there. 1004 Unable to get the OLEObjects property of the worksheet class. Could it be that the macro is in a different workbook? Either way I have no idea how to fix it. Other fixes I found on google haven't helped. I've made sure ActiveX is enabled.
Also, how do I know the button's name? I've assumed it's the "Button 1" that shows up in the top left of excel below the ribbon when I select the button.

 

[jkpieterse]

Almost correct:

Activesheet.Shapes("CommandButton1").Delete

Indeed the name is what you see in the name box to the left of the formula bar.

 

[Logit]

.
You've indicated the CommandButton is located in another workbook.

Option Explicit


Sub DelButton()


Workbooks.Open "TestIt.xlsx"
Sheets("Sheet1").Select


    MsgBox "This is a message."  '<-- place your macro here to replace MsgBox


    Sheets("Sheet1").Shapes("CommandButton1").Delete
    
End Sub

First, I created a workbook named TestIt.xlsx and saved it on the desktop. Placed on Sheet 1 of that workbook, is a CommandButton1 (ActiveX button not a form button).

Then from the main workbook (separate workbook) the above macro was created.

Both workbooks are on the Desktop. This macro should work anywhere on your machine, so long as the two workbooks are located in the same location.

NOTE: where the MsgBox is located in the code, you can replace that with the macro action you want run prior to deleting the CommandButton.

 

[CDienst]

Thanks, Jan, that did the trick.


Logit, would that code work? It was my understanding that using a sheet codename like sheets1 meant that that sheet had to be in the same workbook as the macro.


In my case, the macro is in my personal.xlsb so the book with the button can be an .xlsx.

 

[Logit]

.
I've not heard that before, although it might be true and I'm just not aware of it. However, the macro works as desired here.

I recant the statement that so long as the two workbooks are in the same folder, the macro should work. I just tried it here and that was not the case.
I believe the paths would need to be spelled out precisely for drive / folder/s and filename. I haven't test that part yet.