Microsoft Office Tutorials and References
In Depth Information
Executing Sub Procedures
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:
h ActiveX controls
h Forms controls
h Inserted objects (Shapes, SmartArt, WordArt, charts, and pictures)
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 you can still use them in later versions (which
may be preferable in some cases).
Unlike the Form controls, you can’t use the ActiveX controls 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:
Select Developer ➜ Controls ➜ Insert and click the button icon in the Form Controls group.
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.
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
To assign a macro to a Shape, SmartArt, WordArt, chart, or picture, right-click the object and
choose Assign Macro from the shortcut menu.