Assigning a macro to a button - Excel 2007 and 2003

abdb469
abdb469 used Ask the Experts™
on
If I have created a macro (through the record macro function rather than any coding) and I want to assign that macro to a macro button, how can I do this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Select the button, right click and goto Assign Macro...

You should then see a list of macros and you can pick the one you want to assign.

Note this won't work for an ActiveX button - if you had that type of button you could use it's click event to call the macro.

Commented:
Add a button
Select the button and click on the View Code button on the developer toolbar
Select the click event of the button call the macro by its name

Or

Insert a squire shape into the excel and shape it to be visible as a button
right click on the shape and select assign macro option
select the macro

Author

Commented:
Assign macro is greyed out (Excel 2007) when I right click on the button.

I dont know what you mean by active x button.
NorieAnalyst Assistant

Commented:
How did you add the button to the worksheet?

Was it from the Forms toolbar or the Control Toolbox (ActiveX) toolbar?
NorieAnalyst Assistant

Commented:
Just checked, the only way I can replicate the problem is if I have the button and another control/shape selected when I right click.

Author

Commented:
I figured it out.  You cant just right click on the button.  Have to click on the edge of the button so that it appears in the menu.

I get "Run time error 1004 - cannot use that command on overlapping selection" and it then goes to Selection.Delete Shift:=xlUp in the code.

note I did all of the steps in manually in the macro record function and not by writing code.
The macro simply removes rows and then copies and pastes the select in transpose format.  

I have a few worksheets.
NorieAnalyst Assistant

Commented:
You should be able to just right click on the button.

Anway, as long as you've got it.

As for the error, that's kind of a different question and you would need to post the code for help.

Author

Commented:
Ok.  Please clarify what you mean by an active x button though.  Whats the difference between a normal button from the tool box window and an active x one?
NorieAnalyst Assistant

Commented:
I don't know of a technical definition, but an ActiveX button is similar to the buttons you would use on a userform.

You can't assign macros to them but they have various properties/events you can use.

If you want more specific information you can try googling something like 'Excel ActiveX vs Forms controls'.

That should bring up more information and also comparisons of the two buttons, when to use one type of button instead of the other and so on.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial