Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 82

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 on the Developer tab (see Figure 2.7).FIGURE 2.7 You can find the form controls on the Developer tab.

2 Select the Button 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, as shown in Figure 2.8, activates and asks you to assign a macro to this button.FIGURE 2.8 Assign a macro to the newly added button.

4 Select the macro that you want to assign to the button and then click OK.

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 2.7) work in the same way as the command button in that you right-click and choose Assign Macro to specify a macro to trigger with the control.