564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Writing VBA macros to work with user forms
Part three of a three-part series of blogs
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).
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!
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.|
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!
|Parts of this blog|
25 Aytoun Street