Excel Form Data Validation

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

When creating Excel userforms it seems like a good idea to check the data going into the form before adding it to your sheet.

Once the data is in your sheet you can use other methods like conditional formatting to highlight errors or unwanted data. But it's good to stop the errors before they get into your sheet.

I’m still working with the form I created in my last post Excel Forms, so read this first if you want to know how to create this form.

I am however using a different date picker (calendar) but the functionality of the form remains the same.

Considerations for Excel Form Data Validation

We need to consider a few things:

What data types (form controls) are we using?

If we have a combo box we can easily check the control to see if a valid selection has been made. If we have a text box we may need to write a more complicated routine to check the data.

What data do we consider valid?

With dates, will any date be ok or do we have to work within a date range? If it's a text box, do we have to check for invalid characters or specially formatted strings?

Communicating problems to the user

You need to do this in such a way that it isn’t annoying, but is still clear to the user where the problem is.

You could put a message on screen when you find an error, but as each piece of data must be checked one after the other you don’t want to display a message each time an error is found, that will get very irritating, fast.

What I prefer to do is highlight the data in error by changing the color of the border (or frame) around the problem data. Each control has a border so it’s just a case of setting this border to red when there’s a problem, and setting it back to its original color when there isn’t.

This very quickly gives you a visual indication of where the problems are.

ControlTipText

You can add some text to each control in the ControlTipText area. When you hover your mouse over the control, this text appears. You can use this to communicate what you expect the user to enter into the form.

Validating the data

How and why you validate your data will be determined by what you want the data for. What is your purpose in gathering it? For my form here are my criteria for what I will consider valid data.

Date

This can’t be after today. You can’t incur expenses for a day that hasn’t yet arrived

Client Name and Staff Name

As we are limiting the choices presented to the user by populating these combo boxes with data laid out in tabular format our data validation only consists of making sure something in the list has been selected.

Comments

Must be something. I can’t check to make sure it’s nonsense so as long as something is in there that will have to do.

Receipts Supplied

As with the combo boxes, choices are already limited so as long as either yes or no are selected, we’re good.

Expenses

This is where it gets a bit tricky. As we only have text boxes to take the numeric values we have to write our own code to check if the number is in the format we want.

There is a VBA function IsNumeric but this has it’s limitations. If you pass a string such as $4,4,.0,01$ IsNumeric returns TRUE because it considers the comma, period, and dollar sign as valid formatting of a number, even though we can clearly see that this string is no good for our calculations.

We could also try to convert the string to a number and see if that works by using a function like CDbl. This works pretty well but if we pass it a number like 1.23456789 we get a valid number returned. Whilst the function is correct, we don't want to accept such a number when we are working to only two decimal places. It will also return a valid response if we pass it -1.23 but we don't want negative numbers in this case.

So I need to decide what I want as a valid number. Remembering that when my VBA enters the expenses values into the sheet, Excel will convert the string to a number if it can, I want to provide a string that Excel won’t have any issues with.

I decided that in order to be a valid number a string must :

• Evaluate to > 0
• Not contain a currency symbol
• Not contain any thousand separators
• Contain at most one decimal separator
• Have a maximum of two decimal places

I wrote a function called IsAcceptedNumber which check these things and uses IsNumeric and CDbl. I am pretty happy that what gets entered into my expenses fields is a properly formatted number.

Currency Symbol, Thousands Separator & Decimal Separator

As these can/will vary depending on where you are I’m asking Excel to tell me what they are rather than hard coding a $ , and . as we use in Australia. Hopefully this will ensure the code’s portability between countries.

    CurrSym = Application.International(xlCurrencyCode)
    DecSep = Application.International(xlDecimalSeparator)
    ThouSep = Application.International(xlThousandsSeparator)

Optional Data

Not all the fields on the form need to be filled in. When you made your trip to King’s Landing you probably didn’t travel by air, so there’d be no Airfare expense.

To make sure my code knows which fields don’t have to be completed, I’ve entered Optional for the Tag in each expenses control.

When my code checks the expenses fields I make sure that at least one of these has some data in it.

CheckForErrors : the error checking function

This function checks each control in turn and based on my criteria, counts the number of errors it finds. This error count is returned as the function result. 0 indicating no errors have been found

As well as the IsAcceptedNumber function, CheckForErrors uses two other subs, FlagError and ClearError.

As each control is checked, if the data in it is considered to be in error, FlagError is called. If the data in the control is considered to be ok, ClearError is called

FlagError()

This takes the form control being checked as a parameter. It gives the control a border and changes the color of the border to red

ClearError()

This takes the form control being checked as a parameter. It resets the control's border to it's default color, and the removes the border. It also changes the SpecialEffect on the control to fmSpecialEffectSunken. By giving the control a border in FlagError, this also sets the appearance of the control to flat (fmSpecialEffectFlat) so we need to reset it here.

You could say that I don't need to change the border color back to what it was. Hiding the border is enough. However should I reuse this code at some point in the future and wanted to show the borders, I might be puzzled as to why they are red. I consider it good practice to reset the border color in case this trips me up later and I then spend ages hunting down the code that is making my control borders red.

Entering data into the sheet

The EnterExpenses_Click sub remains as before except for two things, we are entering the date from our new calendar control, and at the top of the sub I’m calling the CheckForErrors function.

If this returns a value greater than 0 (i.e. some errors were found), then the sub exits without entering data to the sheet.

Download the Workbook and Code

Use this link to get a copy of the Excel Form Data Validation .xlsm

Sharing is Caring

If you liked this or know someone who would like to learn how to create Excel forms, please click the buttons below to share it with your friends and colleagues.