Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
Right Click the Button - from the short-Cut menu select "Cut"; Done
"Jwil" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
hi
you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
Hi,
Try this Sub Button1_Click() Dim x x = Application.Caller ActiveSheet.Shapes(x).Select Selection.Cut End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Jwil" wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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 wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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" wrote: 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 wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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 wrote: 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" wrote: 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 wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Delete/Remove Button
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. Jwil wrote: 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" wrote: 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 wrote: 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" wrote: 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 wrote: 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 "FSt1" wrote: hi you need to be in design mode to do that. regards FSt1 "JMay" wrote: Right Click the Button - from the short-Cut menu select "Cut"; Done "Jwil" wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove Macro button help | Excel Discussion (Misc queries) | |||
How to remove all instances of a command button | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
How do I remove an option button from a spreadsheet | Excel Discussion (Misc queries) | |||
Can't remove Radio Button | Excel Discussion (Misc queries) |