Executing a procedure by clicking an object

Excel provides a variety of objects that you can place on a worksheet or chart sheet, and you can attach a macro to any of these objects. These objects fall into several classes:

■ ActiveX controls

■ Forms controls

■ Inserted objects (Shapes, SmartArt, WordArt, charts, and pictures)

Note The Developer Controls Insert drop-down list contains two types of controls that you can insert on a worksheet: Form controls and ActiveX controls. The ActiveX controls are similar to the controls that you use in a UserForm. The Forms controls were designed for Excel 5 and Excel 95, but they can still be used in later versions (and may be preferable in some cases).

Unlike the Form controls, the ActiveX controls cannot be used to execute an arbitrary macro. An ActiveX control executes a specially-named macro. For example, if you insert an ActiveX button control named CommandButton1, clicking the button executes a macro named CommandButton1_Click, which must be located in the code module for the sheet on which the control was inserted.

Refer to Chapter 13 for information about using controls on worksheets. To assign a procedure to a Button object from the Form controls, follow these steps:

1. Select Developer


Insert and click the button in the Form Controls group.

2. Click the worksheet to create the button. Or, you can drag your mouse on the worksheet to change the default size of the button.

Excel jumps right in and displays the Assign Macro dialog box (see Figure 9-4). It proposes a macro that's based on the button's name.


6 t


RuRrtTi j


AaJgn UUirn



it=r. oa

: 'iFafefkr lut riuie'liCWi iW

1 1


3L 11

3-3i isle,







11 H

I I- u



Figure 9-4: Assigning a macro to a button.

Figure 9-4: Assigning a macro to a button.

3. Select or enter the macro that you want to assign to the button and then click OK.

You can always change the macro assignment by right-clicking the button and choosing Assign Macro.

To assign a macro to a Shape, SmartArt, WordArt, chart, or picture, right-click the object and choose Assign Macro from the shortcut menu.

0 0

Post a comment