Macro Execution Button On Worksheet

[Stringmaker]

I've seen Excel worksheets before where the user has a macro embedded as a button on the worksheet space itself and not in the toolbar. Apparently they took a few cells and made some sort of button with them and pointed those to a macro. Could anybody direct how to go about doing this or where this feature is at in the excel help? I found some things on adding buttons to the toolbars but I would rather do this on the sheet as others will be using this and looking in the toolbar isn't an obvious place to have something.

Thanks in advance!
-Brian

 

[GregLocock]

write your macro and save it.

view
toolbars
forms
press the button on the palette that pops up.

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[yogia]

Hi Brian:

I am just going to add to the method that GregLocock has suggested and clarify that a bit.

1) write or record the Macro of interest

2) Click on View > ToolBars >Forms

3) then click on the BUTTON (Command Button) ... the cursor will turn into cross hair

4) draw a rectangle on the spreadsheet where you want to place the macro button

5) the rectangle that you have drawn will turn into a BUTTON (Command Button)

6) Right Click on the BUTTON and assign the related Macro to it.

7) You may also optionally rename the BUTTON as your Macro name


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[joerd]

You will have more options if you use the Controls toolbar. Instead of using "Assign macro" you'll use "View Code" when you right-click it.
I believe the Forms toolbar was native to Excel (since Excel 4 or 5) and stuck around for compatibility, the Controls toolbar is the Office-wide method.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Stringmaker]

Much thanks everyone!! I appreciate your help on this one.

-Brian

 

[TomBarsh]

joerd, Thanks for clarifying that point, I was going to post about it myself. I've never got used to that "new" button (Controls) and now I've been using the "old" method for so long that it's unlikely I'm going to change.

 

[bltseattle]

I find an easy way to add a button is to add a Text Box from the Drawing tool bar, entering the label as the text. Then right-click the selected text box and "Assign Macro"....

It might not be an "official" control button but works very well for this use.

 

[TomBarsh]

bltseattle, that's a good trick to use. Many of the drawing shapes (like the text box) can also have a macro associated with them.

I read once about a trick of drawing rectangles over a cell, turning off all colour and borders for the rectangle so that it is quite invisible, then associating a macro with the rectangle. The result is that "clicking" on the cell runs the associated macro. I use this technique quite often on a particular class of workbooks.

 

[joerd]

That's an interesting thought. You could cover the whole spreadsheet with a transparent rectangle and prevent selection of a cell with the mouse. More power to the keyboard!

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.