Creating a Button

  • Thread starter Thread starter johnF
  • Start date Start date
J

johnF

Hello,

I have created a simple macro with Tools.Macros.Record New Marco. Ho
can I create a button and asing my new macro to it. I wan't to have th
button in the worksheet, NOT a menubar button!

Thanks U al
 
John;

Activate the control toolbox.
Select the commandbutton and drag and drop one on the worksheet.
Right click it and choose view code.

In the middle of the two lines of code which are displayed, paste the code
your recorder had recorded.

That;s it.

Mark.
 
Hi John

I would use a Button from the Forms Toolbar if you are only assigning a
macro. The oens from the Control Toolbox (ActiveX) are a bit much.

Right click on nay toolbar and select "Forms" then simply click the
Button and then your Worksheet. You will then be prompted to assign a
macro.

You can read up on the diff between these 2 types of controls here
http://www.ozgrid.com/Excel/Excel-controls-forms-toolbox.htm

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Go to view then toolbars and select forms.
From forms select button and draw a button on excel worksheet.
Press on right button on the mouse and attach your macro.
I hope it helps..

Hogut
 
This topic addresses exactly what I am trying to do now.

I have created the button within the spreadhseet, I have copied th
macro I had recorded, and I pasted it within the 'sub' and 'end sub
statements after selecting view code.

So far so good...

But when I double click on the button, instead of execution od th
macro, what I get is the VB editing screen with the macro!

Also, when I move the cursor over the new button so I can click on it
the button is 'selected'. Is this OK?,

What am I doing wrong?

thanks!

Ale
 
There are two different types of buttons that you can place on the worksheet.

One is from the Forms toolbar and the other is from the control toolbox toolbar.

If you used the button from the Forms toolbar (but I don't think you did), then
that code would go into a general module and you'd rightclick on the button and
select assign macro. Then point at that macro.

If you used the commandbutton from the control toolbox toolbar, then you should
paste your code into that code window that you see when you double click the
button.

But keep the name that excel generated (something like: "Private Sub
CommandButton1_Click()").

And delete the "sub xxxx()" that you pasted in.

And remember to get rid of the duplicated: End Sub
 
Dave,
Correct; I was using the Control Box.

I tried it at home las evening, and was able to make the 'Form' butto
to work right away. Then, after that, the 'Control' button worke
correctly! :rolleyes:

The only way I was able to get the 'Control' button to work; i.e.
execute the macro instead of displaying the VB editing screen, was t
create the 'Form' button after the 'Control' button. This suggests tha
after I create the 'Control' button I must have to do something t
activate it. I don't know what that is, specifically.

I just tried it here at work, and can't get the 'Control' button t
work properly. I can use the 'Form' button - no problem - but now I a
curious as to what I am lacking to make the 'Control' button work
:confused:

Since I have the 'Form' button properly executing the macro now, thi
is not a critical issue, ;) but when you have a minute, you may b
bale to shine some more light...

:) :) Thanks for your help!!!!!! :) :)

Ale
 
The only thing I of is that you're in Design mode. It's an icon on that same
control toolbox toolbar. If you are in design mode, then your macro won't
run--it won't even start.

Is that what you meant?

If the code starts and fails with a 1004 error and you're using xl97, try
adding:

activecell.activate

to the top of your code.

(Or go into properties and change .takefocusonclick to false. This is a bug in
xl97 that's been fixed in xl2k.)

If code starts, then fizzles out. You may want to post your code--indicate the
problem line(s).
 
Back
Top