Run A Macro From A Button

May 10, 2021 - by Bill Jelen

Challenge: You want an easy way to run a macro. Can you run it from a button on the worksheet?

Solution: There are many ways to run a macro. Figure 106 shows seven different methods in Excel 2003. All of them except the custom toolbar translate to Excel 2007.

Figure 106. You can run a macro from any of these elements.

Method 1: Forms Button

To run a macro from a forms button, in Excel 2003, select View, Toolbars, Forms. Click the Button icon (Figure 107) and then drag in the worksheet to draw a button.

Figure 107. This is how you create the forms button in Excel 2003 and earlier.

In Excel 2007, go to the Developer tab. Open the Insert dropdown and choose the icon shown in Figure 108. Drag a rectangle on the worksheet to draw your button.

Figure 108. If you can’t find the Developer tab in Excel 2007, use Office Icon, Excel Options, Popular, Show Developer Tab in the Ribbon. The Insert dropdown then offers the forms button.

The Assign Macro dialog appears. Click your macro and then click OK.

The button initially appears with the generic name Button 1. To change the name: Ctrl+click the button to select the button without running the macro. The button is surrounded by dots. Drag across the words on the button to select them for editing. The button is now surrounded by diagonal lines. Type new words.

You can change the font, font size, alignment, and color of the button. If you are in Text Edit mode, click the diagonal lines surrounding the button to return to the dots border. Otherwise, Ctrl+click the button to select the button. Press Ctrl+1 to edit the font, alignment, size, and more. Right-click the button and choose Assign Macro to change the macro assigned to the button.

Method 2: ActiveX Button

An ActiveX button looks like a forms button, but it is more flexible, as you will soon learn. Follow these steps to build one in Excel 2003:

Select View, Toolbars, Control Toolbox.
Click the Button icon and drag in the worksheet. Notice in Figure 109 that the Design Mode icon is turned on when you draw a new control. Design Mode is the icon in the top left, with the ruler, triangle, and pencil. Design mode has to be on when you are working with this control. When you exit Design mode, the button you create acts as a button when you click on it.
Figure 109. Design mode must be on to work with the button.
Click the second button, the Properties button, to display the properties for the button. Find the line for Caption. Click in the second column of the Properties dialog, next to the word Caption, and type the words that should appear on the button (Figure 110). Note that you can change the appearance of the button by using properties such as BackColor, ForeColor, and Font. While the Caption property is a simple text box where you can type a new caption, many choices lead to fly-out menus or even to new dialog boxes. You can add a picture to the button, change the color, etc. These features make the ActiveX controls far more flexible than the forms controls. Note that you still have not assigned a macro to the button.
Figure 110. You can change properties in the Properties dialog to change the appearance of the button.
With the button still in Design mode, click the View Code icon. This is the third icon in the Control Toolbox—a magnifying glass looking at a sheet. When you click View Code, you are taken to a brand new macro in the VBA editor. The new macro lives on the code sheet for the workbook.
You can write the macro steps in this new macro. Or, if you want to call an existing macro, simply type the name of the macro as the only code in the macro, as shown in Figure 111.
Figure 111. When you click View Code, you are taken to a new macro on the code pane for the worksheet.

Back in the Excel worksheet, you need to close the Properties dialog by clicking the red X in the upper-right corner. You also need to exit Design mode by clicking the Design Mode icon in the Control Toolbox toolbar. If you are done adding buttons, hide the Control Toolbox by clicking the red X in the upper-right corner.

In Excel 2007, the process is similar. You use the Button icon at the bottom of the Insert dropdown. The Design Mode, Properties, and View Code icons are in the same group on the Developer tab. Microsoft nicely added words so you can easily identify each icon.

Method 3: From Any Shape, Picture, SmartArt, or Clip Art

To set up a macro to run from any shape, picture, SmartArt, or clip art, add an AutoShape or clip art to your worksheet. Right-click the object and choose Assign Macro. You can assign a macro to the shape just as in Method 1 for the forms button.

In Excel 2003, use Insert, Picture to access a number of objects. Choose ClipArt, Picture, or AutoShape. If you are going to use AutoShapes, it is best to display the Drawing toolbar. You can use the icons on the Drawing toolbar to change the color, shadow, text, number of dimensions, and so on.

In Excel 2007, use the Insert tab of the ribbon. AutoShapes have been renamed Shapes. You can also add SmartArt. Right-click the diagram when you are done and choose Assign Macro.

Method 4: From a Hyperlink

Setting up a macro to run from a hyperlink is tricky but possible. To begin, add some text to a cell—perhaps Run the Macro ! . Then choose Insert, Hyperlink and make the hyperlink jump to the cell that contains the text. This basically prevents the hyperlink from going anywhere.

Next, switch to VBA. In the Project Explorer, look for the entry for the worksheet where the hyperlinks are. Right-click that sheet name and choose View Code, as shown in Figure 112.

Figure 112. The code to intercept the hyperlink has to be on the code pane for the worksheet.

There are two dropdowns above the code pane. From the left dropdown, choose Worksheet. From the right dropdown, choose FollowHyperlink.

You now have the makings of a Worksheet_FollowHyperlink macro. Every time someone clicks a hyperlink on this worksheet, this bit of code will run. The Target variable tells you about the hyperlink that was clicked.

If there is only one hyperlink on the worksheet, then you can simply run the macro:

However, if you have multiple hyperlinks on the worksheet, you can use the Target.TextToDisplay property to distinguish between hyperlinks:

Using a hyperlink to run a macro is a favorite trick when you want to toggle between different states. Perhaps you want to toggle between sorting ascending and sorting descending in a column. This macro will perform an action and change the text in the hyperlink so that a different action can be performed the next time:

Method 5: Custom Toolbar (Excel 2003 Only)

In Excel 2003, you can create your own floating toolbar. Choose Tools, Customize. In the Customize dialog, choose the Toolbars tab and then click New. Type a name for the toolbar and click OK.

Keep the Customize dialog open. Go to the Commands tab. In the left dropdown, choose Macros. In the right dropdown, drag either Custom Menu Item or the Custom Button to the new toolbar. Keep the Customize dialog open. Right-click the new icon in the toolbar. You can choose a new icon, choose a ToolTip, and assign a macro to the button (Figure 113).

Figure 113. You can customize the button on the new toolbar.

Summary: There are several ways to start a macro by using controls that appear on a worksheet.

Title Photo: Jens Johnsson on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.