Command buttons

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to do something I haven't done in a long time. Put a command button on a worksheet and assign a macro to the button. I have the button on the sheet OK, but when I get the sub-menu, "Assign Macro" is not in the menu. Before I created the button I recorded the macro. What is my problem? What am I missing? How do I do this? Excel 2003. Thanks.
 
Hi Craig,

You have probably have a control button, not a forms button.

You can't assign a macro to this, it uses event (such as click) code. To
access this,
- open the Control Toolbox toolbar (Tools>Customize>Toolbars)
- go into design mode (click the blue-green triangle icon)
- now double-click the button, and it will take you in to the code event for
click where you can add the code, or even call your macro
- exit design mode

You can call your macro from the command button event code like so

Private Sub CommandButton1_Click()
MyMacro
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Craig said:
I'm trying to do something I haven't done in a long time. Put a command
Click to expand...
button on a worksheet and assign a macro to the button. I have the button
on the sheet OK, but when I get the sub-menu, "Assign Macro" is not in the
menu. Before I created the button I recorded the macro. What is my
problem? What am I missing? How do I do this? Excel 2003. Thanks.
 
Thank you, Bob. As it turns out, my problem was that I was still in Design Mode. I had copied the macro into the click event but still could not get it to work. The button would not "press". What you said in your reply caused to light bulb to come on. Thanks again

Craig

----- Bob Phillips wrote: ----

Hi Craig

You have probably have a control button, not a forms button

You can't assign a macro to this, it uses event (such as click) code. T
access this
- open the Control Toolbox toolbar (Tools>Customize>Toolbars
- go into design mode (click the blue-green triangle icon
- now double-click the button, and it will take you in to the code event fo
click where you can add the code, or even call your macr
- exit design mod

You can call your macro from the command button event code like s

Private Sub CommandButton1_Click(
MyMacr
End Su

--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

Craig said:
I'm trying to do something I haven't done in a long time. Put a comman
Click to expand...
button on a worksheet and assign a macro to the button. I have the butto
on the sheet OK, but when I get the sub-menu, "Assign Macro" is not in th
menu. Before I created the button I recorded the macro. What is m
problem? What am I missing? How do I do this? Excel 2003. Thanks
 
-----Original Message-----
I'm trying to do something I haven't done in a long
Click to expand...
time. Put a command button on a worksheet and assign a
macro to the button. I have the button on the sheet OK,
but when I get the sub-menu, "Assign Macro" is not in the
menu. Before I created the button I recorded the macro.
What is my problem? What am I missing? How do I do
this? Excel 2003. Thanks.
.
Click to expand...
Craig
If you remember the name of the macro you can type it in
the dalog box. If not press ALT + F11 to open vb editor.
You should see Module 1 in the project Explorer (press
Ctrl + R if it is not in view) Double click the module to
view it if neccessary and check the macro especialy the
title and use this for the name. - You might like to test
it first by pressing F5.

If no module exists see if it is in the worksheet code.
right-click on the sheet tab and select View Code but this
is unlikely.
If the code is not there then it might be in another book
especially if you started the recorder while another book
was open.

Regards
Peter
 
Thank you. I will experiment with this method as well. I do know the macro name, so that won't be a problem

Craig,
 
Back
Top