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:

Private Sub TextBox1_Change()

Dim X As String, Str As String
  
       X = TextBox1.Value
       
       'condition 1: allow only numeric entries; first char must not be a dot
      If Not IsNumeric(X) And Not X = "." Then GoBack: Exit Sub
      
       'condition 2: do not allow hyphen, space or entries higher than 1 for the first digit:
      If InStr(1, X, "-") > 0 Or InStr(1, X, " ") > 0 Or Left(X, 1) <> "1" Then GoBack: Exit Sub
      
      'condition 3: allow only 1 dot in string  and no more than 3 decimal points:
      If Len(X) - Len(Replace(X, ".", "")) > 1 Or Len(X) > 5 Then GoBack: Exit Sub
      
      'condition 4: if user types a comma as a decimal separator, replace it with dot separator:
      If InStr(X, ",") > 0 Then X = Replace(X, ",", "."): TextBox1 = X: Exit Sub
      
      'condition 5: allow dot only as the second char:
      If Len(X) = 2 And Mid(X, 2, 1) <> "." Then GoBack: Exit Sub
                      
      'condition 6: allow only values between 1.09 and 1.4:
      If Len(X) > 3 And (Val(X) < 1.09 Or Val(X) > 1.4) Then GoBack: Exit Sub
End Sub

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:

Private Sub GoBack()
#If Mac Then
          MacScript ("tell application " & Chr(34) & "System Events" & Chr(34) & " to key code 51")
#Else
          SendKeys "{backspace}"
#End If
End Sub

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. If Not IsNumeric(X) And Not X = "." Then GoBack: Exit Sub

    –  This is simple, using this we assure that only numeric values are typed, and the first char is not the dot.

  2.  If InStr(1, X, "-") > 0 Or InStr(1, X, " ") > 0 Or Left(X, 1) <> "1" Then GoBack: Exit Sub

    – Because hyphen and spaces will pass the first condition, this condition will make sure they will not be allowed in our text box

  3. If Len(X) - Len(Replace(X, ".", "")) > 1 Or Len(X) > 5 Then GoBack: Exit Sub

    – 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. If InStr(X, ",") > 0 Then X = Replace(X, ",", "."): TextBox1 = X: Exit Sub

    – 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. If Len(X) = 2 And Mid(X, 2, 1) <> "." Then GoBack: Exit Sub

    – Allow dot only as the second char, obviously, it’s the only place for the dot in this example;

  6. If Len(X) > 3 And (Val(X) < 1.09 Or Val(X) > 1.4) Then GoBack: Exit Sub

    – 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:

Private Sub TextBox2_Change()
Dim X As String, R As String
      
      X = TextBox2.Value
       
       'allow only numeric values and slashes
     If Not IsNumeric(X) And InStr(X, "/") = 0 Then GoBack: Exit Sub
     
     'do not allow comma, dots, strings starting with a slash or consecutive slashes:
     If InStr(1, X, ",") > 0 Or InStr(1, X, ".") > 0 Or X = "/" Or InStr(1, X, "//") > 0 Then GoBack: Exit Sub
    
       ' do not allow minus sign, space, or more than 2 nonconsecutive slashes in string:
     If InStr(1, X, "-") > 1 Or InStr(1, X, " ") > 1 Or Len(X) - Len(Replace(X, "/", "")) > 2 Then GoBack: Exit Sub
    
        'allow up to 31 days
        If Len(X) = 2 And InStr(X, "/") = 0 And Val(X) > 31 Then GoBack: Exit Sub
    
        'add slash after days- in case first digit is higher than 3
     If Len(X) = 1 And Val(X) > 3 Then
               X = X & "/"
               TextBox2 = X
      End If
      
        'add slash after days
       If Len(X) = 2 And InStr(X, "/") = 0 And Val(X) <= 31 Then
               X = X & "/"
               TextBox2 = X
      End If
        
       'allow up to 12 months
     If Len(X) <= 5 And InStr(X, "/") > 0 And IsNumeric(Right(X, 1)) Then
           R = ""
           R = Right(X, Len(X) - InStr(X, "/"))
           If Val(Left(R, 1)) > 1 Or Len(R) > 1 Then
                    If Val(R) > 12 Then
                           GoBack
                           Exit Sub
                    Else
                            X = X & "/20"
                            TextBox2 = X
                    End If
            End If
       End If
    
    'allow max. 4 digits for year
     If InStr(X, "/") <> InStrRev(X, "/") Then
           R = ""
           R = Right(X, Len(X) - InStrRev(X, "/"))
           If Len(R) > 4 Then GoBack
    End If
      
      
End Sub

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 *

The Excel Learning Journey that I propose for you, is structured in such a way as to offer you the most practical way to learn Excel, free.

Start Your Free Excel Learning Journey!

Enter your email address to subscribe to this blog: