Page 216 - Excel Data Analysis

11 537547 Ch10.qxd 3/4/03 12:03 PM Page 202

EXCEL DATA ANALYSIS

CUSTOMIZE FORM

CONTROLS WITH MACROS

ou can assign macros to any of the form controls on a The portion of the macro name following the underscore
worksheet to perform custom functions to save you character corresponds to an action, commonly referred to
Y time and effort while analyzing data. For example, if a as an event, which occurs to the control. For example, with
user selects a CheckBox control, you can have Excel total an OptionButton control, the user clicks the radio button to
specific cells. select the option, so the event is Click. If you create a
macro for a ComboBox control, Excel assigns the event to
You can create one macro for each control on a worksheet. the name Change because you want to execute the macro
You create a macro either by recording a series of when the value of the control changes. You use the event
keystrokes or by writing a VBA procedure in the Visual Basic extension of the macro name to tell Excel to monitor the
Editor. When you select the Assign Macro menu option, control and execute the macro whenever a user clicks the
Excel automatically creates a new macro with the name of control.
the control followed by an underscore and an event name,
such as _Click. Excel assigns the control name to the No matter which option you select — recording or writing
control when you add it to a worksheet. For example, the VBA — Excel assigns the same name to the macro. See
first OptionButton control that you add to a worksheet is Chapter 9 for more information on creating macros.
named OptionButton1. If you create a macro for the
option button, Excel gives the macro the name
OptionButton1_Click.
CUSTOMIZE FORM CONTROLS WITH MACROS

OptionButton26_Click

⁄ Right-click the selected ■ A menu appears. ■ The Assign Macro dialog ‹ Click New to create a
control. box opens. new macro in the VBA Editor.
¤ Click Assign Macro.
■ Excel assigns a default ■ You can click Record to
macro name for the selected record a macro.
control.
Note: See Chapter 9 for more
information on macro creation.
202