 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
When to Apply Validation
In the last lesson we looked at validating a users input via the means of various Methods and Functions. This type of validation can be of benefit to the user as well as ourselves. However, as I also said in the last lesson, in many cases it is virtually impossible to account for all possible erroneous data. Possibly a close exception to this rule would be when we use a ListBox or ComboBox and allow the user to choose their entry from the control. By using this method we can ensure that the data entered in that control is a valid entry, although we still cannot guarantee it the correct valid entry!. We also looked at how it was possible and quite easy to add a users input that is not part of that list. The only danger in doing this is the possibility that the entry that gets added to the list is not valid for the list it is being added to.
What we shall look at in this lesson is when to reject or accept a users entries. By this I mean should we allow the user to completely fill out the UserForm first and then inform him/her that some of their entries are not valid, or would it be better to inform the user at each step. Take for example the Wizard which we designed earlier. We placed in some simple validation that checked whether a required entry was made and did not allow the user to move forward to the next step until such time. While this would be the logical way to validate a Wizard, it may not be the case for another type of UserForm.
Control Events
As you are now aware, each control that we place on to a UserForm has its own set of events. The default events for most controls is the Click event or the Change event. The quickest and easiest way to find out the default event for a particular control is to place it on a UserForm and then simply double-click it. This will force Excel to automatically take you to the default event procedure for that control. Obviously in most cases it would not be feasible to try and validate a control as soon as the user clicks it.
Before we can make an informed decision on which event to use to fire our validation check, it is important to understand that the events will happen in a particular order. You may recall in Lesson 1 on UserForms we looked in detail at how the Initialize event of the UserForm is the first event to fire when a UserForm is loaded. The second event is the Activate event. Unfortunately, when dealing with controls the order of events is highly dependent on the action that the user takes. However, having said this it need not have any great bearing on our timing of validating a users inputted data. Lets now look at a simple example to see what I mean.
On a UserForm place two TextBox controls. Double-click TextBox1 to get to the Change event procedure for that TextBox, then place in the code below.
Private Sub TextBox1_Change()
If TextBox1.Value = VBNullString then Exit Sub
If Not IsNumeric(TextBox1.Value) Then
MsgBox "No text please", vbCritical
TextBox1.Text = vbNullString
End If
End Sub
Do the same for TextBox 2
Private Sub TextBox2_Change()
If TextBox2.Value = VBNullString then Exit Sub
If IsNumeric(TextBox2.Value) Then
MsgBox "No numbers please", vbCritical
TextBox1.Text = vbNullString
End If
End Sub
In the above two examples we have used the Change event of the TextBox to ensure the user cannot enter anything which should not be entered. For example, the user would be informed immediately if he or she tried to enter text into TextBox1, or a number into Textbox2. For cases like these, the decision is relatively simple. It would make no sense to allow the user to type in a string of text only then to inform him/her later on that the entry is not valid. Unfortunately, development and validation of UserForms is rarely this simple. If, for instance, we placed the code below into our TextBox for validation it would be virtually useless. Notice also that we have used a simple IF statement as the first line of code in the procedure above. This is to check whether the TextBox is empty or not. If this code was not in there, our error message would display twice, once if the user entered invalid data and again when we change the TextBox text to the VBNullString. This is because the code setting the TextBox to VBNullString will itself fire the Change event.
Private Sub TextBox3_Change()
'Some no good code
If TextBox3.Value = VBNullString then Exit Sub
If InStr(1, TextBox3, "@", vbTextCompare) = 0 Then
MsgBox "Not an email address, vbCritical"
End If
End Sub
If you haven't already guessed, the reason why it would be no good is because the validation check would fire as soon as any entry is made into the TextBox. This means the InStr function will return 0 (zero) until such time as the @ symbol is typed. It is in cases like these that we need to use another event to run our validation check. One event which can be used quite often for this type of check is the Exit event. Before we look at the Exit event though, it is important that we understand fully the meaning of the term Focus. According to Microsoft Excel's VBE Help:
The ability to receive mouse clicks or keyboard input at any one time. In the Microsoft Windows environment, only one window, form, or control can have this ability at a time. The object that "has the focus" is normally indicated by a highlighted caption or title bar. The focus can be set by the user or by the application.
Now that we have put a definition to the word Focus, we can look at how the Exit event can be used to run a validation check. The least you need to know to use the Exit event efficiently is that the Exit event will fire immediately before a control looses Focus to another control on the same form. There is, however, a small trap here which can catch the unaware developer out. The best way to show you this is with a small example.
On a UserForm place two TextBox controls.
Double-click one of them and then select the Exit event from the Procedure drop down box to the top right of the Private module.
Within this Exit event type: MsgBox "Goodbye".
Now, go back to your UserForm and run it.
Place your mouse insertion point within the TextBox that has the code in the Exit event.
Select the other TextBox and your MsgBox should display.
Now close the form and on the UserForm place a Frame control, then move the TextBox that is housing the Exit event code into the Frame Control. Again, run your UserForm and repeat as we did above. When you exit the TextBox that houses the Exit event code, and click in the TextBox that does not have the code, the Exit event will not fire. This is something you should be aware of, placing controls in a Frame control can often cause unexpected results. The bottom line here is, if your controls are within a Frame control, do not use the Exit event for your validation check. At while we are on the Subject, ControlTiptext will not display if the Control is in a UserFor, although this seems to not be the case in Excel XP.
Let us now use the Exit event in a TextBox which is not within a Frame control. In the Exit event for any TextBox place the code below:
Private Sub TextBox4_Exit(ByVal Cancel As MsForms.ReturnBoolean)
If InStr(1, TextBox4, "@", vbTextCompare) = 0 Then
MsgBox "Not an email address, vbCritical"
Cancel = True
End If
End Sub
Just be sure that you change the two occurrences of TextBox4 to the name of your TextBox. When you run the UserForm now, and type in any text that does contain the @ symbol, you can quite easily move into any other TextBox or control on the UserForm. However, should you type anything in the TextBox which does not contain the @ symbol, and then try to click into another control, the MsgBox will display and Focus will remain with the TextBox that should have an email address within it. The part of the code which does not allow the user to shift Focus to any other control is the Cancel argument. This is because we have set it to True which is canceling shifting Focus away from our TextBox. What this will do is force the user to type an @ symbol in the TextBox and they will not be able to activate another control until they have done so. Obviously if you use a method similar to this, there are two important things you need to consider.
You must tell the user why they are unable to leave the control
You must also decide whether they will be allowed to leave to control should the control contain no text at all. By this I mean that if they simply click in TextBox4 and then click on another control, the MsgBox will be displayed and they will be trapped in that control.
In regards to point 2, possibly the best way to handle this situation is to have the control contain a default value and then reset the control back to its default value should they type an invalid entry. This is shown in the example below:
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If InStr(1, TextBox4, "@", vbTextCompare) = 0 Then
MsgBox "Not an email address, vbCritical"
Cancel = True
Me.UndoAction
End If
End Sub
What I have done in the above example is, while in the VBE at design time I placed a default value for TextBox4 this was "YourEmail@YourAddress". I then used the Cancel argument set to True to prevent the user from leaving the control initially and then used the UndoAction property of the UserForm to place the default value back into TextBox4. This way the user will not become trapped in the TextBox unnecessarily. The UndoAction simply reverses the most recent action that supports the Undo command. Naturally, if we use this method we must still at some time check whether the user has entered a valid email address. In the case of the control being on a MultiPage control, this would probably be when they tried to change the page or if the TextBox is simply on a UserForm, we may do this when they exit another control or possibly Exit or Enter another control. The Enter event is similar to the Exit event and is described with the Exit event within the VBE Help.
Tag Property
The Tag Property for a control can be used for many different purposes, but in this case we will use it to flag particular controls that we would like to check once the user has finished entering their data. For this exercise follow the steps below:
Insert a UserForm into the VBE of Excel.
On this UserForm Place 6 TextBox controls.
Set them up directly underneath each other.
From top to bottom change their Name Properties to: HomeEmail, WorkEmail, Age, PayNumber, Telephone and StAddress
Again, from top to bottom change their Value Property to: *YourEmail@Home, *YourEmail@Work, *Your Age, *Your Pay Number, Telephone and Street Address
Now in the Tag Property of the HomeEmail, WorkEmail, Age, PayNumber TextBoxes type the word Check
At the top of the TextBoxes place a Label control and change it's Caption Property to: Please note * denotes required entry
Finally add a CommandButton and change it's Caption Property to: OK
Ok, if you haven't guessed already we are going to code the "OK" button so that the user must fill in all required entries. To do this we will use the Click Event of the CommandButton.
Private Sub CommandButton1_Click()
For Each ctrlControl In Me.Controls
If ctrlControl.Tag = "Check" Then
ctrlControl.SetFocus
ValidationCheck 'Private Sub to check entries
If bCancel = True Then Exit For
End If
Next ctrlControl
If bCancel = True Then Exit Sub
'<Some carry on code here>
End Sub
This code will Loop through all Controls on the UserForm and if its Tag Property is Check our code will enter the If Statement where it first sets focus to the Control and then runs a Procedure that will do the actual checking to see if the TextBox has been filled in. I will show and explain this Procedure next. After the Procedure "ValidationCheck" has run it returns to the loop with either True or False parsed to a Boolean Variable (bCancel). If the variable is True the code exits the loop and immediately encounters the line:
If bCancel = True Then Exit Sub
Which stops the code doing anything further. As we have used a variable at the Module level, we must declare it at the very top of the Private Module.
Dim ctrlControl As Control
Dim bCancel As Boolean
Note that we also have a variable declared as a Control, this is used in the For Each Loop. Remember a For Each Loop always loops through an Object collection using a variable. In this case it's the Controls of the UserForm!
Let's now look at the Procedure "ValidationCheck"
Private Sub ValidationCheck()
Dim ValToCheck, strMessage As String
ValToCheck = Me.ActiveControl.Value
Select Case ActiveControl.Name
Case "HomeEmail", "WorkEmail"
If InStr(1, ValToCheck, "@", vbTextCompare) = 0 Then
strMessage = "Please enter an email"
ElseIf ValToCheck = "*YourEmail@Home" Or _
ValToCheck = "*YourEmail@Work" Then
strMessage = "Please enter an email"
End If
Case "Age", "PayNumber"
Select Case ValToCheck
Case vbNullString, "*Your Age", _
"*Your Pay Number", Not IsNumeric(ValToCheck)
strMessage = "Please check your age or pay number"
End Select
End Select
If strMessage <> "" Then
MsgBox strMessage, vbCritical
bCancel = True
Else
bCancel = False
End If
End Sub
Let's look at this one line-by-line
We must make our Procedure a Private Sub as it is residing in the Private module of the UserForm.
The variable "ValToCheck" is declared as a Variant (default) as we will be parsing both text and numbers to it. The "strMessage" will be used to parse a String to and used in a message box.
We use a Select Case Statement against the ActiveControl name (this is why we Set focus to the Control with the "Check" Tag Property in the loop.)
We know the name of our controls (as we named them aptly) so we use this to determine which Control (or TextBox) has Focus (AvtiveControl).
If it's one either "HomeEmail", "WorkEmail" we can use a simple If Statement to check whether the user has entered an "@" or not. If they haven't we parse a simple String to our variable.
We also check to ensure the original default value is not there.
Next we use our Case to check if the ActiveControl is the Age or PayNumber Textboxes. If it is, we immediately enter another Select Case Statement to find out 1. It it's blank, 2 and 3. If it still has it's default value and 4. It it contains text.
If any of the above are True we parse some relevant text to our variable.
Once our code has left the original Select Case Statement we check if our String Variable is empty or not.
If it's not, we know that the user has not correctly filled in the Form. So we display a message box containing our text.
As soon as the user clicks Ok on the message box we parse True to our Boolean variable and our code returns to the Loop.
One of the other advantages to setting Focus to our control is that the cursor will be flashing in the appropriate Control, leading the user to his/her mistake. Although having said this Excel 2000 seems to have some bugs with this.
Summary
So as you can see there does need to be some foresight as to which Control is best suited to a particular situation. At times you may even use both the ComboBox and ListBox.
Always give some thought to when you are going to check the users entries. Don't allow the user to fill in loads of TextBoxes etc only to be told at the last minute he/she got them all wrong. At the same time though, don't immediately check the entry of a user if the entry depends on the entry in another control. Foresight, planning and testing many times is the best way. It is also a very good idea to allow a person that knows nothing about what should be entered, to beta test it for you. This is a good way to bring out the bugs. I have my wife do this for me as she has an uncanny knack in causing Run-Time errors :o)