Validate Input From A Dialog

You must validate the values specified for controls on a dialog box before passing the values back to your procedure. When you validate the data values, you do so for two major reasons. First, you ensure that the user specifies a value for a control. If the user forgets to select a control value, you can remind them immediately. Second, and probably most importantly, you ensure that errors do not occur in your code because the wrong type of data passes to a procedure.

Then statement, to check the properties of each control. This ensures that they have the appropriate values. For example, if you want to make sure that the user typed a string in the Name text field on the dialog box, you can add the following If Then statement to your procedure: If TextBoxi.Text = " " Then.

You can create code that checks the user input for any event that occurs on the UserForm. The easiest place to do so is prior to closing the dialog box. For example, if you have a CommandButton control, such as an OK button, that passes the values to global variables and closes the dialog box, you may consider this the ideal place to validate your data. When you place the validation code in that routine, you need to use a conditional statement, such as an If

This If Then statement checks the Text property for the specified TextBox control and ensures that it contains a value. If the property is empty (there is nothing in it), your VBA code can call the MsgBox function to display a message indicating that a value must be specified.

Besides checking for values, you can also use the VBA validation functions to verify that the control contains the appropriate data type. For example, the statement If Not

IsNumeric(TextBoxi.Value) Then ensures that the user typed a number in a TextBox control.

VALIDATE INPUT FROM A DIALOG BOX

VALIDATE INPUT FROM A DIALOG BOX

Excel Vba Input Dialog Box

'—n On the UserForm, doubleclick the control that you want to validate the data values.

■ Typically an OK command button is a good location for validating data values.

-■ The code window opens with the cursor at the beginning of the Click procedure for the selected control.

'—n On the UserForm, doubleclick the control that you want to validate the data values.

■ Typically an OK command button is a good location for validating data values.

-■ The code window opens with the cursor at the beginning of the Click procedure for the selected control.

CUSTOMIZING DIALOG BOXES, MENUS, AND TOOLBARS

F>X ra You can use the UserForm events to launch validation code, as shown in the following code that captures the QueryClose event to ensure a value was selected for a ListBox control prior to the dialog box closing.

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, as shown in the following table.

CONSTANT

VALUE

DESCRIPTION

vbFormControlMenu

0

User selected the Close button on the dialog box.

vbFormCode

1

The code initiated an Unload statement.

vbAppWindows

2

The Windows operating session is ending.

vbAppTaskManager

3

The Windows Task Manager is closing Excel.

□ Switch to Excel and run the macro.

■ The Message Box displays if a value is not selected for the control.

0 Type If ListBoxl.ListIndex =

-1 Then, replacing ListBoxl.ListIndex = -1 with the control and property value to check.

—0 Type MsgBox "Select a value", replacing "Select a value" with the text for the MsgBox.

□ Switch to Excel and run the macro.

■ The Message Box displays if a value is not selected for the control.

+1 0

Post a comment