"Deadline-Beating Blueprints Trusted by Intermediate and Advanced Excel Users"

How to run a macro from a button

You can run an Excel macro by clicking any Excel object you can imagine: text box, shape, picture, chart, etc. See below the main Excel objects you can use…

Chart, text box, smart art, picture, etc.

After storing your Excel macro for future use, you can attach your macro to any button inside any workbook.

Here’s the 3-step process to do it…

1) Create the button in your sheet

Go to the ribbon Insert>Illustrations>Shapes>then, add any of the available shapes. See the picture below…

Add any shape to assign a macro to

You can also insert any other type of illustration like: Picture, Clip Art, SmartArt, etc. You can use any already-created button. Additionally, you can also use other Excel objects like charts, equations, etc.

Once the shape/object is in your sheet, you are ready to link an Excel VBA macro to it.

2) Assign a macro to your button/object

Right click over the existing shape or object and then choose “Assign Macro…” on the contextual menu. See the picture below…

Assign macros to shapes, clip art, smart art, charts, etc.

Then, specify the source location of your macro in the “Macros in:” drop-down list of the “Assign Macro” dialog (“This Workbook”, “Personal Macro Workbook” or any specific workbook). See the picture below…

Choose the location of the macro and click OK

Important notice: if your macro is not stored centrally in the Personal Macro Workbook or as an Add-in, you need to open the macro source-workbook before you assign those macros to any button. Read “how to store Excel macros for future use

Then, select the Excel macro you want to be attached to the button, and then click OK.

Your Excel macro is now attached to your button!

You can also create a new macro that will be automatically attached to the current button. Just click “New” or “Record…” in the “Assign Macro” dialog.

You are almost done, just a final step to make your Excel macro button more reusable…

3) Name your Excel macro button meaningfully

Don’t use names as “Click here” or “Macro 1”, use meaningful names that describe the macro purpose, for example: “Delete selected columns”, “Insert financial formula”, etc. This way you will use the button naturally and without hesitation.

To do it, proceed this way…

Right click over the shape (Text Box, Shape, Equation), then click “Edit text”, then enter your text.

In the case of pictures and charts you can add a Text Box to describe the assigned-macro, it could be something like “click the picture to format columns”, etc.

And be creative with the layout of the button, have fun. Select the shape and then go to Drawing Tools>Format>Shape Styles, there you can change the shade, color, borders, etc.

Here are some examples of Excel macro buttons…

Format your macro buttons in Excel using colors, borders, 3d effects, etc.

Have button ideas to share? Let us know at the comments section below…