location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.
To set up a trusted location, follow these steps:
1. Select the Macro Security button on the Developer tab.
This activates the Trust Center dialog box.
2. Click the Trusted Locations button.
This opens the Trusted Locations menu (see Figure 1-6), which shows you all the directories that are considered trusted.
3. Click the Add New Location button.
4. Click Browse to find and specify the directory that will be considered a trusted location.
FIGURE 1-6: The Trusted Locations menu allows you to add directories that are considered trusted.
After you specify a trusted location, any Excel file opened from this location will have macros automatically enabled.
Most user-created macros are designed for use in a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the Personal Macro Workbook so that they’re always available to you. The Personal Macro Workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn't exist until you record a macro using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list (refer to Figure 1-1).
If you store macros in the Personal Macro Workbook, you don’t have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.
When you create macros, you may want to have a clear and easy way to run each macro. A basic button can provide a simple but effective user interface.
As luck would have it, Excel offers a set of form controls designed specifically for creating user interfaces directly on spreadsheets. There are several different types of form controls, from buttons (the most commonly used control) to scrollbars.
The idea behind using a form control is simple: You place a form control on a spreadsheet and then assign a 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, when the control is clicked.
Take a moment to create a button for the AddTotalRelative macro you created earlier. Here’s how:
1. Click the Insert button under the Developer tab. (See Figure 1-7.)
2. Select the Button Form Control from the drop-down list that appears.
3. Click the location where you want to place your button.
When you drop the button control onto your spreadsheet, the Assign Macro dialog box, shown in Figure 1-8, activates and asks you to assign a macro to this button.
4. Select the macro you want to assign to the button and then click OK.
FIGURE 1-7: You can find the form controls in the Developer tab.
FIGURE 1-8: Assign a macro to the newly added button.
At this point, you have a button that runs your macro when you click it! Keep in mind that all the controls in the Form Controls group (shown in Figure 1-7) work in the same way as the command button, in that you assign a macro to run when the control is selected.
FORM CONTROLS VERSUS ActiveX CONTROLS
Notice the form controls and ActiveX controls in Figure 1-7. Although they look similar, they’re quite different. Form controls are designed specifically for use on a spreadsheet, and ActiveX controls are typically used on Excel user forms. As a general rule, you should always use form controls when working on a spreadsheet. Why? Form controls need less overhead, so they perform better, and configuring form controls is far easier than configuring their ActiveX counterparts.
You can also assign a macro to a button in Excel’s Quick Access Toolbar. The Quick Access Toolbar sits either above or below the Ribbon. You can add a custom button that runs your macro by following these steps:
1. Right-click your Quick Access Toolbar and select Customize Quick Access Toolbar.
This opens the dialog box shown in Figure 1-9.
2. Click the Quick Access Toolbar button on the left of the Excel Options dialog box.
3. Select Macros from the Choose Commands From drop-down list on the left.
4. Select the macro you want to add and click the Add button.
5. Change the icon by clicking the Modify button.
FIGURE 1-9: Adding a macro to the Quick Access Toolbar.
Examples of Macros in Action
Covering the fundamentals of building and using macros is one thing. Coming up with good ways to incorporate them into your reporting processes is another. Take a moment to review a few examples of how macros automate simple reporting tasks.
The most common use of macros is navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create some sort of a switchboard, like the one shown in Figure 1-10. When a user clicks the Example 1 button, he’s taken to the Example 1 sheet.
