Delete/Remove Button


J

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
 
Ad

Advertisements

J

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
 
S

Shane Devenshire

Hi,

Try this

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

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)
 
Ad

Advertisements

J

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
 
D

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
 
J

Jwil

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

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top