This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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). Coding Forms - the Basics Form and Control Events 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.

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

Coding Forms - the Basics
Form and Control Events
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 27 February 2012

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!

Parts of this blog
Coding Forms - the Basics Form and Control Events Validating Form Data (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Validating Form Data

The BeforeUpdate and AfterUpdate Events for a Control

Choosing when to Validate - Form or Control Level?

Head office

London

Manchester