Excel Dashboards and Reports: How to Run Your Macros
Macros are very useful for your Excel dashboards and reports. To see your macros in action, select the Macros command from the Developer tab. The dialog box shown in this figure appears, allowing you to select the macro you want to run. Select the AddDataBars macro and click the Run button.
If all goes well, the macro plays back your actions to a T and applies the Data Bars as designed; see the following figure.
You can now call up the Macro dialog box again and test the RemoveDataBars macro shown in the following figure.
When you create macros, you want to give your audience a clear and easy way to run each macro. A button, used directly on the dashboard or report, can provide a simple but effective user interface.
Excel Form controls enable you to create user interfaces directly on your worksheets, simplifying work for your users. Form controls range from buttons (the most commonly used control) to scroll bars and check boxes.
For a macro, you can place a Form control in a worksheet and then assign that macro to it — that is, a macro you’ve already recorded. When a macro is assigned to the control, that macro is executed, or played, each time the control is clicked.
Follow these steps to create buttons for your macros:
Click the Insert drop-down list under the Developer tab.
Select the Button Form control, as shown in this figure.
Click the location you want to place your button.
When you drop the Button control into your worksheet, the Assign Macro dialog box, as shown in the following figure, opens and asks you to assign a macro to this button.
Select the macro that you want to assign (in the figure, it is “AddDataBars”) and click OK.
Repeat Steps 1 through 4 for any other macros.
The buttons you create come with a default name, such as Button3. To rename your button, right-click the button and then click the existing name. Then you can delete the existing name and replace it with a name of your choosing.
Keep in mind that all the controls in the Form Controls menu work in the same way as the command button, in that you assign a macro to run when the control is selected.