Page 224 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

CAPTURE INPUT FROM

A CUSTOM DIALOG BOX (CONTINUED)

ou create VBA code to monitor the events of a procedures. For example, you typically call a UserForm from
control, such as a user clicking a button, and to another procedure to capture user responses and then pass
Y determine when Excel should execute specific code. the values back to the main procedure.
Each control has its own specific events that you can You must declare public variables at the top of your
capture, and the Visual Basic Editor keeps track of those for module, before any procedure code, using the Public
you. You can quickly create an event procedure in the code statement. Doing so enables you to declare variables that all
window by selecting the appropriate control name in the procedures within a project can access.
Object list box and then selecting the corresponding event
from the Procedure list box. When you select an event, the For a procedure, you can reference specific controls on a
Visual Basic Editor creates a procedure with the name of the UserForm by specifying the name of the UserForm followed
control followed by the event name. by the name of the control. You can set additional
properties for a control before displaying the UserForm
All control values on a UserForm are only active as long as directly within you code. You can also use the With
you have the dialog box open. If you close the dialog box statement to shorten the code required to set properties for
prior to saving user input values, you lose the user input. To an object. See Appendix C for more information about the
avoid any potential problems with lost data, consider saving syntax of the With statement.
user responses to global variables that can pass into other

CAPTURE INPUT FROM A CUSTOM DIALOG BOX (CONTINUED)

° Create a new module. · Type Public UserSelection — Type With ± Type .AddItem "Jan",
As String, replacing UserForm.ListBox1, replacing replacing "Jan" with the
Note: See Chapter 9 for information UserSelection with the UserForm and ListBox1 value to add to the ListBox
on creating modules to store name of the global variable. with the names of the control.
macros. UserForm and ListBox
‚ Create a new subroutine. ¡ Repeat step 12 for each
controls, respectively.
item.
™ Type End With.
210