Excel First

Excel Solutions

Excel User Form Assistant

Force users to type in Text Boxes only what you expect…

Excel User Forms are a great way to collect data in a specific structure. Unfortunately, there is a major limitation that you have to take into consideration when choosing between a worksheet form or a User Form:  if utilizing a User Form for data entry, there is no built-in data validation for Text Boxes, or Combo Boxes… (you can only limit the number of characters, which might not be what you want)

The solution is quite simple:

To see how the solution works, download the test workbook: Real Time user form assistant.xlsm

The User Form from the test file has 2 simple Text Boxes.

For the first Text Box, we can assist users when typing data, to avoid invalid entries, using the TextBox1_Change Event.

The VBA code will allow only numeric values between 1.09 and 1.4, with no more than 3 decimal places (for example, 1.102 will be accepted by code, same to 1.400; An example of a rejected value is: 1.401, the code will send a backspace key to remove only the last digit typed).

Here is the VBA code:

The purpose of the GoBack procedure is to undo the typing of the last wrong character, which is much better than clearing the entire value, you will not have to start typing again the entire value.

The GoBack procedure used in the above code is:

As you can already see, it’s based on SendKeys method, it will work on a Mac too.

I do have to mention that I’m not a fan of SendKeys method, I usually avoid it because the method is hard to control, but it works without failure in this situation.

Those six conditions from the TextBox1_Change event are doing all the job. Let’s take a closer look:

  1. –  This is simple, using this we assure that only numeric values are typed, and the first char is not the dot.
  2. – Because hyphen and spaces will pass the first condition, this condition will make sure they will not be allowed in our text box
  3. – This condition is meant to limit the number of dots a user can type (we allowed dots in our string, but if our beloved user types only dots? 🙂 ) . Only one dot is allowed, and only 5 chars are allowed in our string: this means that we can input no more than 3 decimal places (first 2 chars are for the integer and the decimal separator)
  4. – This condition is strictly related to Regional Settings, for users who have comma as a decimal separator. This condition will convert the comma to a dot separator to avoid further errors (in VB, the default separator is the dot separator)
  5. – Allow dot only as the second char, obviously, it’s the only place for the dot in this example;
  6. – If the string passed through all previous condition, this is the main “gate”: if the final value is not in the chosen interval, the user will not be able to type wrong values.

Be careful of the order of these condition, it might not work as expected if the conditions are not in this order.

Second example:

Real Time Date entry Assistant

The next text box is another example of how we can control, and really help users while typing in a date field. Most likely, you will use a date picker, but if the form will be used on multiple computers, with different versions of Excel, not to mention Excel for Mac, the date picker is a real pain, unless it’s a calendar created in VB. Anyway, you can play with this example of how to assist users to type in a valid date.

Here is the code for TextBox2_Change event:

You will notice that I used the TextBox1_Exit and TextBox2_Exit events, as a second “barrier”, just in case our inventive users find a way to bypass all restrictions.

Finally, we have the users inputting the correct information 🙂

If you were able to bypass all those “barriers” to type a wrong value, or if you found this tip useful, please leave a comment.

Leave a Reply

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