Creating a Button

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

johnF - 09 Mar 2004 07:57 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Mark Rosenkrantz - 09 Mar 2004 08:46 GMT

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.

Signature

More Excel ? www.rosenkrantz.nl or contact@rosenkrantz.nl
-------------------------------------------------------------------

> Hello,
>
[quoted text clipped - 6 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

johnF - 09 Mar 2004 09:04 GMT

Thanks :-

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Dave Hawley - 09 Mar 2004 09:08 GMT

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

Reply to this Message

:...Hamdi OGUT...: - 10 Mar 2004 13:13 GMT

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

> 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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Ingeniero1 - 15 Jul 2004 21:46 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Dave Peterson - 15 Jul 2004 23:09 GMT

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

> This topic addresses exactly what I am trying to do now.
>
[quoted text clipped - 18 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message

Ingeniero1 - 16 Jul 2004 14:11 GMT

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

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Dave Peterson - 16 Jul 2004 22:29 GMT

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

> Dave,
> Correct; I was using the Control Box.
[quoted text clipped - 24 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message