Assigning Code to Controls

After you place a control on a worksheet, chart sheet, or dialog sheet and establish its initial properties, you can assign a Visual Basic procedure to the control.

Whenever an action (also called an event) occurs on the control, Microsoft Excel runs the procedure assigned to the control. The following table describes the events that the various types of control respond to.

Controls	Event

Command buttons, group boxes, check boxes, option buttons	The user activates the control. This event is used for controls that have no value or that have only a simple value, such as True or False.
Edit boxes, list boxes, drop-down boxes, combination list-edit boxes, combination drop-down edit boxes, scroll bars, spinners	The user changes the control. This event is used for controls that support a complex value.
Dialog frames	The dialog box appears.

For example, if you assign a procedure to an edit box, the procedure is called whenever the user enters or changes data in the edit box. When you assign a procedure to a dialog frame, the procedure runs when the dialog box is first made visible.

Note

You can assign any Visual Basic procedure to a control. Functionally, there's nothing special about the procedure name; creating a procedure called "Button4_Click" doesn't automatically assign the procedure to Button4 — you must use the Assign Macro dialog box or the OnAction property to assign the procedure to the control.

You can also assign code to drawing objects (such as ovals and rectangles). For more information, see Chapter 11, "Automatic Procedures and OnEvent Procedures."

Using the Assign Macro Dialog Box

The Assign Macro dialog box allows you to specify the procedure that runs when an event occurs.

To associate an existing procedure with the event belonging to a control

1. Select the control.

2. On the Tools menu, click Assign Macro.

You can also click the control using the right mouse button (to display the shortcut menu) and then click Assign Macro.

3. In the Macro Name/Reference box, enter the name of the procedure you want to assign to the event.

To create a new procedure and associate it with the event belonging to a control

1. Select the control.

Edit Code

2. On the Forms toolbar, click the Edit Code button.

Microsoft Excel opens a Visual Basic module scrolled to display an empty procedure.

3. Between the Sub and End Sub statements, write the procedure you want to associate with the event.

To edit a procedure previously associated with the event belonging to a control

1. Select the control.

Edit Code

2. On the Forms toolbar, click the Edit Code button.

Microsoft Excel opens a Visual Basic module scrolled to display the associated procedure. If the associated code belongs to the Microsoft Excel 4.0 macro language, Microsoft Excel switches to the macro sheet containing the associated macro.

3. Edit the code.

Using Visual Basic

The OnAction property sets the name of the procedure that runs whenever an event occurs. The following example causes the StartDialog procedure to run whenever the dialog box first starts and causes the ButtonPressed procedure to run whenever the user clicks a button on the first dialog sheet.

With DialogSheets(1)
    .DialogFrame.OnAction = "StartDialog"
    .Buttons.OnAction = "ButtonPressed"
End With

Tip

The ButtonPressed procedure can use the Caller property to determine which button was clicked. For buttons, the Caller property returns the button name.