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).

  1. Coding Forms - the Basics
  2. Form and Control Events
  3. Validating Form Data (this blog)

This blog is part of our Excel VBA tutorial blog.  If you want classroom training (for small groups of up to 6 people), take a look at our courses in VBA.

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:

Event Notes
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

Case Else

'not a valid person: report error, cancel event and leave

'mouse pointer flashing in text box

MsgBox "No such person"

Me.txtName.SetFocus

Cancel = True

Exit Sub

End Select

End Sub

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!"

Me.txtName.SetFocus

Exit Sub

End If

'check drink chosen

If Len(Me.cmbDrink.Text) = 0 Then

MsgBox "You must specify a drink!"

Me.cmbDrink.SetFocus

Exit Sub

End If

'transfer to spreadsheet ...

Worksheets("Orders").Select

'(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!

  1. Coding Forms - the Basics
  2. Form and Control Events
  3. Validating Form Data (this blog)
This blog has 0 threads Add post