Introduction
Creating your Macro
Creating your Shape
Assigning the Macro to your Shape
Running your Macro
Comments


Introduction

The last several blog posts have been pretty advanced. Today we’re going to take it down a notch by going back to some VBA basics.

There are over a dozen ways to run an Excel macro, including with VBScript and from the top ribbon. Today I’ll focus on running macros by clicking an object in your spreadsheet.

To be more specific, in this tutorial I’m going to show you how you can assign a macro to a shape in Excel. Doing this will allow you to quickly run your macro by simply clicking the shape in your spreadsheet instead of having to navigate through all the menus to run it.


Creating your Macro

Let’s assume you’ve opened your VBA editor and created the following short public macro:

Sub ColorCell()
ActiveCell.Interior.Color = vbYellow
End Sub

Your time is valuable. It's time to become a VBA expert.

This macro changes the background color of whatever cell you have selected to yellow. Obviously your macro can be more complicated than this!

The important thing is that your macro cannot be a private macro. Just don’t put the word Private in front of the word Sub or pass your macro an argument and you’ll usually be okay.


Creating your Shape

Now, go over to your spreadsheet and insert a shape, like this:

Insert Shape in Excel

The shape can be whatever you like and you can put it wherever you want. I made a rectangle shape so it looks like a button.

You can color the shape however you want and you can even add effects and enter text into your shape.

After designing it, my final shape looks like this:

Edit Shape in Excel


Assigning the Macro to your Shape

Now you’re ready to assign the macro you created earlier to the shape you just created. Remember, my macro was titled ColorCell.

To associate your shape with your macro, right-click your shape in Excel and click “Assign Macro…”

Right Click > Assign Macro

As long as your macro isn’t a private macro the name of the macro should appear in a window that looks like this:

Assign Macro to a Shape in Excel

The default macro will probably say something like “Rectangle1_Click,” but that’s not what you want. To finish assigning YOUR macro, click the name of your macro in the dialog box, then click the OK button.

Assign Macro to a Shape in Excel


Running your Macro

The only thing left to do is to test out your button. Clicking your shape will now run your macro. It’s as easy as that!

Clicking Shape Runs Macro

Each time I click my shape, the cell I have selected changes to yellow.

You can give your shape some neat effects with more advanced macros, like this macro that makes your shape look like it’s been pressed when clicked.


Comments

I want to hear your feedback about types of content you’ve been seeing here lately. Let me know if you like it when I mix in a few beginner guides with the more advanced guides on this website.

Share this article on Google+, Twitter, and Facebook to help get the word out. Sharing on social media is how I’m able to reach and teach more people about the awesome power of VBA.

Also, please stop by my Excel Add-ins page for some great products. This is my primary source of blog revenue and the money I receive from sales helps pay to keep this website online and accessible for all to learn.

I want to thank all my readers who have already subscribed to my email list and I encourage you to go ahead and subscribe if you haven’t done so. You’ll love freebies and the fun VBA content I send your way!

As usual, reach out to me via my VBA Consulting page to get your VBA questions answered.

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too


Your time is valuable. It's time to become a VBA expert.

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.