My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Excel Form Data Validation
excel form data validation
by

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.

Excel form data validation animation

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.

Related Posts: I've written other posts on Excel Forms which might interest you.

Excel Forms Creating a user form

Excel Date Picker (Calendar) Using a VBA class to implement a date picker. This is more robust than an ActiveX control and doesn't require anyone who uses your workbook to install additional controls or software.

Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.

Searching for Data With a User Form. Create a userform to search for data on the worksheet.


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.

Control tip text

Control tip text in action

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.

Control Tag Optional

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

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
excel form data validation

More Userforms Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel
Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
add data to combo box drop down list in excel form
If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
Excel forms, insert, update, delete data
Insert, update and delete data in an Excel table with a user form.
excel date picker
An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
excel userforms
Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls
Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag:

Reader Interactions

Comments

  1. Karl

    This is a very nice tool with which I can work very well as a beginner!! A big praise to you…

    How can I ensure that no uplikates are recorded when saving the data in the table?

    Many greetings

    Reply

    • Catalin Bombea

      Hi Karl,
      That depends on your definition of duplicates, as some users don’t have a unique ID to identify a row, for some users a unique row might be identified by a combination of columns.
      Anyway, you’ll have to write a custom code to store the existing ID’s with the corresponding row number in a dictionary, when saving you add a new row if record does not exist or get the row number if record exists.
      We can help you, please open a new topic on our forum and upload a sample file.

      Reply

  2. John

    Very nice post. I’m just unclear on one piece, and so this is a bit confusing to me at the moment.

    I don’t see any reason for using both the CDbl and IsNumeric functions in the IsAcceptedNumber routine, like it does currently. I feel like you could get away with just one or the other. By using IsNumeric and ensuring there are no currency or thousands separators, you would be set at that point and not need get any additional value out of the error checking with the CDbl function, correct? Similarly, by using the CDbl function and verifying that the result of this function is not negative or more than 2 decimal places, there wouldn’t be any need for the IsNumeric function at that point, as I understand it.

    Instead, I’m thinking you could pick one or the other approach, and either way you would know that you are accepting a string that doesn’t have any letters or special characters. So, it looks to me like there currently are a couple additional, redundant checks in this routine that aren’t really necessary (although from a learning standpoint, it is nice to see both; so a user could choose whichever they prefer).

    Let me know if I’m missing something here. I just want to make sure I understand these functions and whether there is something to be gained by using both, so that I can properly error check any solutions I may implement and account for any deficiencies in these functions.

    Thank you

    Reply

    • Catalin Bombea

      Hi John,
      You can setup real time restrictions to a textbox, instead of checking the output. You can restrict what they are allowed to type into that box, see an example here.

      Reply

  3. Kweku Mensah

    I need help, Folk.

    I am completely new with VBA.

    I am trying to do a searchable drop down list with the list in another worksheet.

    I got this code online. The worksheet had both combobox and the source data list on the same sheet.

    Private Sub ComboBox1_Change()
    Dim i As Long
    For i = 1 To Application.WorksheetFunction.CountA(Sheet1.Range(“A:A”))
    If LCase(Left(Sheet1.Cells(i, 1), 1)) = Me.ComboBox1 And Me.ComboBox1 “” Then
    Me.ComboBox1.AddItem Sheet1.Cells(i, 1)
    End If
    Next i
    Me.ComboBox1.DropDown
    End Sub
    Private Sub CommandButton1_Click()
    Me.ComboBox1.Clear
    Me.ComboBox1 = “”
    Me.ComboBox1.SetFocus
    End Sub

    I however want to :

    1. The code refers to Sheet1, my WorkSheet have been other names rather than Sheet1, Sheet2, and I want to review the code to reflect the names of my worksheets.

    2. I wish I could review the code so that the combobox will be on one sheet and the source list will be on another sheet.

    Reply

    • Philip Treacy

      Hi,

      I’ll need to see your workbook and source data. Please post a topic on the forum and supply the workbook.

      Regards

      Phil

      Reply

  4. Chris J Polnau

    This is awesome saves me a lot of time. I am just a beginner so i appreciate it. I was hoping to adapt the form and change things around how would i go about doing that?

    Reply

    • Philip Treacy

      Hi Chris,

      Hard to give you an answer without knowing exactly what it is you want to do.

      Can you please open a topic on the forum and supply more information and we’ll see what we can do.

      Regards

      Phil

      Reply

    • Catalin Bombea

      Hi Zarak,
      A defined table has specific elements. To mention some of them: table header row, table body and table totals row.
      Whenever you add a new row, it will always be added to the table body, not to any of the other elements, as there cannot be 2 or more header rows or totals rows.
      You might be looking at it as a normal range (which is not), that’s why you might expect that adding a row should be added at the first empty row. A table is an object, you can move it around the sheet and drop it anywhere, it has special functionalities, you have to change the way you look at it.
      There is no way to lock a totals row.

      Reply

  6. Fong

    My excel version doesn’t have the “Microsoft Monthview” in the additional control so I can chose or have that same option.

    Reply

  7. Oscar Katsinis

    Hello. The CheckError routine has an error; When a TextBox is text-type and not empty, it is validated by the IsAcceptedNumber routine, which marks it as an error. I’ll try to correct it. Thanks a lot.

    Reply

    • Catalin Bombea

      Hi Oscar,
      Not sure what you mean by “When a TextBox is text-type”, the output of a text box will always be a text string, even when you type only numbers in it. Can you show us an example of a number that is marked as error?

      Reply

    • Philip Treacy

      Hi Michael,

      The first date picker I used was a 32bit ActiveX control provided by Microsoft. However not everyone would have this installed, so to distribute your workbook may have been a problem.

      I instead used a date picker which came as a VBA class. This is easily incorporated into your project and distributed without problem.

      I did a blog post you can read about the new Excel Date picker

      Phil

      Reply

  10. Julian

    Hi Philip,

    In addition to my previous question, after several trials I found both Date and Mid function were not workable on your file I downloaded from this blog in my Excel 2007. I tried to replace function Date with Now and it’s sucessful but I didn’t know which function could replace Mid similarly to run the Function IsAcceptedNumber as you coached. What’s I really confused was both Date and Mid were built-in functions of VBA, right? I tested these two funcitons individully, instead of the whole project you wrote, in Excel 2007 and it did work well. Could you please tell me what’s the problem I encountered? Thanks again.

    Julian

    Reply

    • Philip Treacy

      Hi Julian,

      That is odd. Yes both Mid and Date are VBA functions and should work for you too. Replacing Date with Now shouldn’t work though (and didn’t work for me when I tested it) as Now returns a variant in the form 

      14/07/2015 9:07:22 AM

      but the calendar expects just the date part of that.

      What I did find worked in place of Date was this 

      DateValue(CStr(Now))

      I don’t understand what is wrong with Mid though. Can you please open a Helpdesk ticket and send me the workbook you are using, just so I can check whether it works on my computer or not.

      Thanks

      Phil

      Reply

      • Graham Stent

        Hi Phil,

        I had the same issue and it was caused by a missing reference. I did Debug/Compile VBAProject and it showed me:
        “MISSING: Microsoft Windows Common Controls-2 6.0 (SP4)”

        I removed the tick from the checkbox for this reference and recompiled and all worked fine after that. This also fixed the “Date” issue.

        Cheers,
        Graham

        Reply

  11. Allen

    Phil, this is a very slick usability solution! In some of my earlier projects, I liked using a light red background to signal blank fields. But the red border is much more flexible, in that it can signal any type of error (and probably is less annoying!)

    For text validation, I go the extra mile, using the Regular Expression class. I don’t know if you or this site’s philosophy prohibits use of references, but Microsoft VBScript Regular Expressions 5.5 is very useful.

    Finally, this has nothing to do with your tutorial and examples, but I have a pet peeve about form validation: consider password fields. Why do most forms assume you know how long the maximum password can be? For that matter, why do most of them not tell you whether or not you can include symbols, upper case, digits, etc.? If that irks you, try to remember that when you design your forms: a small reminder above each field, or a brief instruction box at the top of the form could reduce a lot of validation issues.

    Cheers,

    Mitch

    Reply

    • Philip Treacy

      Thanks Allen.

      You are right I’d be using RegEx too if I knew the format of the text to expect.

      Couldn’t agree with you more on password fields! I have been known to shout at the screen when I’ve submitted a form and only then it tells you that you’ve done it wrong and empties all the data you’ve just typed in, grrr.

      Phil

      Reply

  12. Julian

    Once the function “IsAcceptedNumber” was called I got the error message “The project or the program liabary not found”; Meanwhile the string “MID” in the following code ” If ((Len(Mid(Ctrl.Value, InStr(Ctrl.Value, DecSep), 1)) – 1) > 2) ” was also being highlighted. What happened? Please kindly advise. Thank you very much.

    Reply

  13. Ranjeet Kumar

    Hi Team,

    i am unable to understand sum, count(subtotal), please let me help on this topic.

    Regards,
    Ranjeet Kumar

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *