Page 227 - Excel Data Analysis

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

FORM CONTROLS 10

Instead of creating validation code that runs based on control events, you can
use the UserForm events to launch code, such as capturing the QueryClose
event for the UserForm. The following code ensures that a value was selected
for a ListBox control prior to the closing of the dialog box.

Example
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If Not IsNumeric(TextBox1.Value) Then
MsgBox "Must be a number"
Cancel = 1
End If
The QueryClose event has two arguments, Cancel and CloseMode. The
Cancel argument accepts an integer value. If the value of the argument is
anything other than zero, the QueryClose event stops, and the associated
dialog box remains open. The CloseMode argument contains a constant value
indicating the cause of the QueryClose event. You can change the value of
this argument by typing the value 0-3 associated with the constant or using
one of the four constant values. Use vbFormControlMenu to specify the user
selected the Close button on the dialog box; use vbFormCode to indicate the
code initiated an Unload statement; use vbAppWindows to denote that the
Windows operating session is ending, and that the Windows Task Manager is
closing Excel.

¤ Type the control and › Type Exit Sub. Á Switch to Excel and run ■ The message box appears
property value to check. the macro. if a value is not selected for
ˇ Type End If. the control.
‹ Type MsgBox and follow it
with the text for the MsgBox.
213