BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages).
Posted by Andy Brown on 28 February 2012
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Validating Form Data
The most time-consuming part of any forms-based system is preventing errors. This is much harder with a form than with a worksheet, because you have less control over what a user types in.
To make user forms more robust, make extensive use of combo boxes to limit input options and spinner buttons to automate entry of numbers. The less freedom you give your user, the better!
The BeforeUpdate and AfterUpdate Events for a Control
Imagine a user is filling in a form, and has just typed his or her name into a textbox called txtName. Suppose the user is about to press the Tab key to go onto the next textbox in a form. At this point the following events will happen for the txtName textbox:
|BeforeUpdate||Occurs before the control value is "saved"|
|AfterUpdate||Occurs immediately after the control value is saved, and the old value discarded|
|Exit||Occurs when you leave the control|
However, you can interrupt the BeforeUpdate event if you're not happy with what the user typed in. For example, you may know that there are only 3 people working in your company who can use your form:
Private Sub txtName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
'if this person isn't one of company employees, report error
Select Case LCase(txtName)
Case "rita", "sue", "bob"
'any of these are OK
'not a valid person: report error, cancel event and leave
'mouse pointer flashing in text box
MsgBox "No such person"
Cancel = True
Every one of the 4 things we do if we find an error is vital:
|Line of code||Heading 2|
|Displaying message||If there's a problem, you need to tell your user what it is!|
|Setting the focus||There's nothing more annoying than a system which tells you you have an error in one place, and leaves your cursor flashing in another.|
|Cancelling the event||If you don't cancel the BeforeUpdate event, the form will from this point on work with the new value, even though you've reported an error.|
|Exiting the subroutine||You want to ensure that any other code in the BeforeUpdate validation code doesn't run.|
Choosing when to Validate - Form or Control Level?
The above example assumes that you validate each control as you leave it. Personally, however, I find this annoying, and I prefer to wait until I've filled in the entire form before being shown what I've done wrong. To do this, you could attach code to the Click event of the main button for a form. For our drinks order example, here's what this could look like:
Private Sub cmdOrder_Click()
'check name chosen
If Len(Me.txtName.Text) = 0 Then
MsgBox "You must say who is ordering drink!"
'check drink chosen
If Len(Me.cmbDrink.Text) = 0 Then
MsgBox "You must specify a drink!"
'transfer to spreadsheet ...
'(code continues processing successful order)
This may all look fairly straightforward, but how about if the form includes the number of sugars: how do you then check this is a sensible number? Or indeed that it IS a number? Or how about the order date? Your validation can end up running to several screens of code. This is where advanced controls like combo boxes, spinners and the like come into their own!