Microsoft Office Tutorials and References
In Depth Information
Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out
One nice feature of the Exit event is that if you set the Cancel argument to Tr ue , the user will
be unable to switch the focus to a different control until the text box contains a proper date.
Remember that you also need to set the background color to Window Background
(&H80000005) if there isn’t an error, to reset any previous error conditions. This is handled
by the Else clause.
The final technique used in this application prevents errors by substituting a combo box con€
trol in place the text box control for State . Because the user is limited to choosing one value
from the provided list of values, it becomes impossible to enter invalid data.
By setting the MatchRequired property of the combo box control to Tr ue , the user will be
prevented from leaving the control until the input matches one of the values in the List . Another
way to ensure that only a valid value is selected is to set the Style property of the combo box
control to fmStyleDropDownList , which forces the control to operate as a list box, where the
user can only choose a value from the specified list of values in the drop-down list instead of
typing a value that might not be on the list.
In either case, a routine like the following AddStates routine is necessary to initialize the
combo box control. This routine would typically be called from the user form’s Initialize event.
On the CD
The full list of the states can be found in the AddStates routine in the sample
Private Sub AddStates()
For more information on validating data, see “Getting Data Entry Right the First Time” on page 187.
Displaying the User Form
The final step in this process is to create a simple macro that displays the user form. In this
case, adding the following subroutine to the ThisWorkbook object in the Visual Basic Editor is
all that’s required to show the form. Any time the user wants to use this form, he simply has
to run this macro.
Public Sub ShowForm()