Running a Macro from a Command Button

September 01, 2004

You've written the perfect macro using Excel VBA. Rather than having to remember a shortcut key, you would like to add a button to the worksheet to invoke the macro.

There are two types of buttons that you can place on a spreadsheet. While ActiveX controls have more features, I often use a regular command button. It is easy to set up, provided you make a simple modification to your toolbar first.

Right-click any toolbar and choose "Customize..." from the bottom of the pop-up menu. The Customize dialog has three tabs. Choose the Commands tab.

In the left listbox, scroll down and select Forms. Then, in the right list box, you will see the third option is called a Button. Click the Button item in the right listbox, drag, and drop it on an existing toolbar.

The button is now added to your toolbar. You can now close the Customize box.

To draw a button on the worksheet, you will click the button icon in the toolbar. Click in the worksheet and drag to draw any size button. When you release the mouse, the "Assign Macro" dialog will appear. You can now select which macro should run when you click the button

You'll notice that the button has a not-very-friendly name like "Button 1". Right click the button to have the selection handles appear. You can now click and drag to select the text on the button. Type something descriptive.

Extra Tips: While editing text on the button, you will notice that the button is surrounded by diagonal lines. If you right-click the lines and choose Format Control..., you can edit the font on the button.

Note that this Format Control dialog has only a tab for the font.

This is very subtle. If you go back to the button and click in the area of the diagonal lines, the lines change to dots. Right-click the dots, choose Format Control and you will find that the Format Control dialog has many more options.

Using Autoshapes: You can also assign a macro to any autoshape. From the menu, select View > Toolbars > Drawing to display the Drawing toolbar. Use the Autoshape icon to select an interesting shape. Drag in the worksheet to create the shape. Right click the shape to access the Assign Macro option.