I’ve written a couple of blogs lately about creating Excel macros. In a related topic, today I’m going to talk about one of the easiest and most intuitive ways of running your macro: a button. Before we get to that, though, let’s review some of the methods of running your macro. You can use any of the following methods:
· The Macro dialog box (Press “Macro” on the “View” ribbon)
· Shortcut keys, assigned when you create the macro
· Customizing the ribbon, adding your own button to it
· Adding a button to your worksheet
There are other ways, but these are my favorites. I’ll cover each of these options in a separate blog. Today, we’re going to focus on the last one: the button.
Creating a Button
This is a great enhancement to your Excel application, giving you (or another user) a quick and visual way to run your macro. In my sample sheet, it looks like this:
By clicking the button, you don’t need to go looking for your macro on a list, or remembering a shortcut key. You simply click the button, and the assigned macro runs.
At a high level, here are the steps you’ll follow to set this up:
· Record your macro
· Add the button to your worksheet
· Assign your macro to the button
· Optionally, format the button
Sounds easy, right? It is!
For purposes of today’s discussion, I’m going to assume that you’ve already recorded a macro. Let’s jump right into the second step: adding the button to your worksheet.
Adding the Button
In order to add the button, you’ll need to display the “Developer” ribbon in Excel. It looks like this:
If you don’t see this ribbon in Excel, don’t worry. That’s typical. By default, this ribbon is hidden, since most people won’t need it. You can follow these steps to turn it on:
From the “File” menu, choose “Options”:
Note: sometimes the “Option” button is under a “More…” link, as you see here:
Either way, once you click “Options”, you’ll then click “Customize Ribbon” on the left navigation bar:
After you click “Customize the Ribbon”, look at the right side of your screen. You’ll see a list of tabs. Most are checked off, but the “Developer” option is probably not checked. That’s why you’re not seeing the Developer ribbon.
Check off “Developer”, and then click “OK”. You should end up back on your worksheet, with the “Developer” ribbon available. Click on it to activate it, and take a minute to get familiar with it.
Now, locate the “Insert” button on the Developer ribbon, and click on it. You’ll see a list of Form Controls and ActiveX Controls appear, as I’ve shown here:
Notice the little tip that says “Button (Form Control)”? That’s displayed because I am hovering over the first button under “Form Controls”. Clicking here activates the Button tool. Go ahead and click it.
Now, move your mouse over your worksheet, and you’ll see that your cursor turns into a “+”:
That “+” is showing you where the button will appear on your worksheet. Move your cursor until the “+” is where you want the button, and then click the mouse.
When you do, a dialog box will pop up, asking you which macro this button should run:
Click on your macro name in the list, and click “OK”. Note: I have quite a few macros listed, but you may not. This list will vary depending on how many macros you've developed.
The button will then be displayed on your worksheet. Mine looks like this:
It’s got a default font and default text, which we’ll fix later. For now, test it out by clicking the button. Your macro should run. I have my button set up to run a simple macro that inserts the company name in cell A1 and “Expense Report” in A2, as you can see here:
Although my macro worked, you can see that the button is interfering with the text. Let’s take care of that.
Moving the Button
To move the button, you have to select it. Normally in Excel, you select a cell by clicking on it. This won’t work with the button, though, since clicking the button will run the macro instead.
To select the button without running the macro, hold down the “Ctrl” button on your keyboard, and while holding that, click the button with your mouse. You can release the “Ctrl” button after you click. Once selected, the button will appear with little bubbles on its corners and edges, like this:
Now that it’s selected, you can move it. To nudge it, use the arrow keys on your keyboard. For a bigger move, you need to use your mouse, but it’s a little tricky to get your mouse in just the right position. You need to position your mouse on one of the button’s borders, but not on any of the bubbles. When you get it in the right position, you’ll see your mouse pointer change into a four-headed arrow, like this:
Click & hold the mouse button down when your cursor looks like this, and you’ll be able to drag the button to a new location.
Having trouble with that? Yeah, a lot of people do. It’s tricky to get the mouse in just the right position. It might be easier to use those little bubbles to resize the button. Read on for those instructions.
Resizing the Button
With the button selected, you can use those little bubbles at the corners and edges to resize the button. Click and hold the bubble on the middle right, and drag to the right to make the button wider, for example. If you click on a bubble that’s on a corner, you’ll be changing two dimensions at once – for example, if you click the bubble on the bottom right, you can change both the height and the width at the same time. The bubbles on the edges (between the corners) only change one dimension at a time.
As with the previous discussion about moving the button, you’ll need to carefully watch your mouse pointer when you want to resize the button. When you hover your mouse over one of those bubbles, your cursor will turn into a two-headed arrow. Those arrows show you which direction you can drag to resize. Here’s an example:
I can click & hold the mouse button down over this bubble, and either drag left to made the button narrower, or drag right to make the button wider. I’ll make it wider, dragging that bubble out to column D:
You may notice that the text changed in my example. It now says “Button 3”. Actually, it didn’t change. The “3” was cut off earlier, and now my button is wide enough to show it. Later, we’ll change that text.
If you had trouble moving the button but were able to make it wider, you can now use the bubble on the left-center to make it narrower. I will drag mine to column C, for example:
As you can see, this was an effective way to “Move” the button without having to get that elusive four-headed arrow.
Changing the Text
You’ll want to change the text on the button to make it clear what it does. In my case, I’ll put a simple “Run Macro” text, but you may want to be more descriptive. Some examples: “Run Report”, “Add Headings”, “Reformat”.
To change the text, right-click on the button, and choose the “Edit Text” option:
When you do this, you’ll see a cursor flashing before the first letter. Use the “Delete” key on your keyboard to erase the current text, and then type your new value. Use your mouse to click on any cell on the worksheet to save your new text. Here’s what mine looks like now:
Lots of people have asked me how they can change the color of the button, or the color of your text. For now, I’m going to tell you that you can’t. It’s not technically true, but it requires some advanced techniques that are probably not worth the effort. If you’re determined to do it, your best bet is to learn to use the ActiveX Command Button object, and write VBA code to go along with it. As I said, that’s a going to be a learning curve, so ask yourself how much its worth.
You can change the font and the font size of your button, though.
To do so, select the button, using the Ctrl-Click technique discussed previously. Once it’s selected, you can use your usual buttons to change the text:
Here you can see that I used the “Forte” font, and set the size to 18 points. You can also apply Bold, Italic and Underline formatting.
That’s it!
Thanks for joining me on this journey to figure out how buttons work in Excel. This is one of my favorite methods of running macros. I hope you’ll find it useful too!
In a future post, I’ll discuss how to run a macro from a button on your ribbon – also a cool feature. As a little preview, here’s what that could look like:
If you subscribe to my mailing list, you’ll receive a notification when I post that blog.
As always, don’t hesitate to reach out if you’ve got questions!
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2022. All rights reserved.