Place a button on a worksheet and assign a macro to it?

[PFB]

How do I place a single "button" on a worksheet (say, in one cell), and
assign a macro to it?

 

[Shane Devenshire]

Hi,

Choose View, Toolbars, and pick the Forms toolbar. Click the Button tool
and then click in the spreadsheet. You will be prompted for the macro you
want to assign. You can resize and move the button after assigning the macro.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Don Guillett]

I often use shapes from the drawing tool bar. OR, even pictures.

 

[PFB]

Hi,
Sounds simple, but I am using Excel2007 (Vista) and I can't find "Toolbars"
or "Forms" on the ribbon under View, and the help function has not "helped".
What am I missing?

Thanks, Paul

 

[Dave Peterson]

You have to show the Developer tab

I _think_ it's under
Office button|excel options|Popular

Then click on the Developer tab|Controls Group|Insert icon

You'll be able to choose between a button from the Forms toolbar (on top) or a
commandbutton from the control toolbox toolbar (on the bottom).

You want the top one.

 

[kasbrown]

Hi,

Choose View, Toolbars, and pick the Forms toolbar.  Click the Button tool
and then click in the spreadsheet.  You will be prompted for the macro you
want to assign.  You can resize andmovethe button after assigning the macro.

If this helps, please click the Yes button

Cheers,
Shane Devenshire





- Show quoted text -

How do I move the button and macro? When I move the button, the macro
stays in place it was created. I would like to add rows to my
worksheet, but when I do all of my macros are askew.

Thanks,
Kasandra

 

[Dave Peterson]

The macro stays assigned to the button no matter where you move it.

If your macro relies on the position of the button, you could check in your
code.

For instance:

Option Explicit
sub testme()
dim BTN as button 'from the Forms toolbar!
set btn = activesheet.buttons(application.caller)
msgbox btn.topleftcell.address 'just to show you.

'select the row that contains the topleftcell of the button
btn.topleftcell.entirerow.select
end sub

 

[dalymjl]

How do I place a single "button" on a worksheet (say, in one cell), an

assign a macro to it?

Create you macro first. Now make sure you have the "Drawing" toolba
visible (View/Toolbars/Drawing). Next create the button on th
spreadsheet using either the "Rectangle" or "Oval" option on th
Drawing toolbar. Then right-click on the button and click on "assig
Macro". Select the macro you want to execute when the button i
pressed. You can put text on the button or colour it, if you like.


regards

mj