Delete/Remove Button

[Jwil]

I want to call or assign a macro using a command button or a button from the
forms toolbar. How do I remove or delete the button using VBA after the
macro it called is done? It doesn't really matter which button I have to use.

Thanks

 

[JMay]

Right Click the Button - from the short-Cut menu select "Cut"; Done

 

[FSt1]

hi
you need to be in design mode to do that.

regards
FSt1

 

[Jwil]

Hi,

Thank you for the replies. Maybe I wasn't as clear with my question.
I need to remove the button within the Macro.
So the scenario goes like this.:
The user hits the button and the macro runs.
After the macro the button goes away so the user doesn't
hit the button again. and so that the worksheet can be saved without
a button in it.

Thanks

 

[Shane Devenshire]

Hi,

Try this

Sub Button1_Click()
Dim x
x = Application.Caller
ActiveSheet.Shapes(x).Select
Selection.Cut
End Sub

 

[Dave Peterson]

Going through the buttons collection:

Option Explicit
Sub testme()

Dim BTN As Button
Set BTN = ActiveSheet.Buttons(Application.Caller)

'your code to do real stuff
MsgBox "hi"

BTN.Delete

End Sub

If you think that you'll ever need that button again, you may just want to hide
it (btn.visible = false)

 

[Jwil]

Hi Dave,

I can't get it to work yet but I'm not entirely sure where to place the
code.
When I double click the button in Design Mode it takes me to the
"Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)"
sub routine. That is where I have the code to run my other macro:
"call Macro1"
I tried using Shane's suggestion here and it gives me an error:
Run-time error '13': Type Mismatch
on the "ActiveSheet.Shapes(x).Select" line.

I used your suggestion in a routine by itself and it gives me an error that
says
Run-time error '1004': Unable to get the Buttons property of the Worksheet
class. on the "Set BTN = .....etc." line.

Thanks

 

[Dave Peterson]

Your button is not from the Forms toolbar. You used a commandbutton from the
Control Toolbox toolbar.

You can either remove that commandbutton and replace it with a button or you
could use something like:

Under the worksheet module:

Option Explicit
Private Sub CommandButton1_Click()
'your code here
MsgBox "hi"

Application.OnTime Now + TimeSerial(0, 0, 1), "DeleteTheCommandButton"
End Sub

In a General module:

Option Explicit
Sub DeleteTheCommandButton()
Worksheets("sheet1").OLEObjects("CommandButton1").Delete
End Sub

 

[Jwil]

It worked.
I wanted the macro to run on a double click so I went with a command button.
Thanks for the help.

 

[Dave Peterson]

Glad you got it working.

But as a user, I think I'd be confused about doubleclicking on a commandbutton.
It doesn't seem natural to me.

It worked.
I wanted the macro to run on a double click so I went with a command button.
Thanks for the help.