Page 208 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

AN INTRODUCTION TO FORMS

ou can use forms in Excel to capture and organize combined with macros, or you can use VBA to create
data values. Excel provides built-in data input forms, custom dialog boxes that display and capture information
Y but you can also create custom forms. You can design for a worksheet.
forms in a worksheet using the available form controls

Data Input Forms
If you want a form to capture data values for a list, you must add the initial records to the list before selecting
use the Data ➪ Form option. When you click this this option.
option, Excel displays input fields that match the current
data list. You can add new records to the list by entering You can also use the Data ➪ Form option for locating
values in the fields and clicking New. Because Excel different values in a data list. See Chapter 2 for more
creates the data entry form from the current list, you information on using this option.

Custom Forms VBA Dialog Boxes
You can design a form directly in a worksheet to You can create a custom dialog box within the VBA
capture data values. You can add different form controls Editor that opens when a particular macro executes. For
to capture data; a form control is a graphic object that example, you can create a dialog box that appears
enables the user to perform an action, such as selecting when a workbook opens by first creating the dialog box
a value. Common controls include check boxes, radio and then creating a macro called Workbook_Open. See
buttons, and list boxes. You add controls to a worksheet Chapter 9 for information on creating a macro that
by using the Forms toolbar. You can assign different executes when a workbook opens.
macros to a control to customize the worksheet based
on a control selection. As with Excel form controls, you assign VBA controls to
the custom dialog box. You select the different controls
For most form controls, you must define the range of from the Visual Basic Editor Toolbox. After assigning the
cells containing the input values for the control. You can appropriate graphic image for the control, you need to
have the input values in the current worksheet or any create the VBA code that corresponds to the control.
other worksheet that you can access. You also specify For example, if you add a list box to a custom dialog
the cell that receives the selection from the control. box, you must specify the values to display in the list
box by creating a VBA procedure.
You can control the selection in a field on a form by
applying data validation rules to the cell. For example, if
you want a cell to contain a value only between 10 and
100, you specify a data validation rule for the cell. If you
specify a value outside the validation range, Excel
displays an error message. See Chapter 3 for more
information about creating a data validation rule.

194