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.
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.
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
Enter your email address below to download the sample workbook.
Use this link to get a copy of the Excel Form Data Validation .xlsm
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
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.
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
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.
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.
Hi,
I’ll need to see your workbook and source data. Please post a topic on the forum and supply the workbook.
Regards
Phil
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?
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
How you locked the last Total Cell. And Data is new data is adderd above it. Kindly Tell Me.
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.
My excel version doesn’t have the “Microsoft Monthview” in the additional control so I can chose or have that same option.
Hi Fong,
Use the Date Picker instead of the built in calendar, you will avoid other problems as well.
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.
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?
Hi Phil,
Why the change in the date picker and what did you change it to?
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
As usual, great post :). Thanks Philip
Thx Tomislav 🙂
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
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
but the calendar expects just the date part of that.
What I did find worked in place of Date was this
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
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
Thanks Graham. I find it odd that removing this reference fixes problems calling the Date function!
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
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
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.
Hi Team,
i am unable to understand sum, count(subtotal), please let me help on this topic.
Regards,
Ranjeet Kumar
Hi Ranjeet,
Do you mean the sum and subtotal in the tables(in my workbook), or what you see at the bottom right of the Excel status bar? If it’s in the tables then please read our article on Excel tables
Regards
Phil
Hi Ranjeet,
You can find tutorials on those functions here:
https://www.myonlinetraininghub.com/excel-formulas
But I recommend you take our free training to get your Excel skills up to speed:
https://www.myonlinetraininghub.com/free-registration
Kind regards,
Mynda