TextBoxes on userforms in Excel VBA

When you work with VBA macros in Excel, you often need user input via a userform. It can be text (e.g. a name) or numeric values, and the TextBox control is ideal for this.

You put one or more text boxes on your userform and ask the user for input. Afterwards you have to check, if the user "followed the rules". If you just assume that he typed a numeric value, your program will crash, if he wrote something else.

Below is a simple example on how to check the content of a textbox. That is laborious, if you have a lot of textboxes to check the same way, so afterwards I show how to check the smart way using a class module.

To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.

The simple control

Imagine a userform with a textbox (TextBox1) and a command button. The user is asked to type a numeric value in the textbox and then click the OK button.

To check the user input you put the following code in the command button's click procedure, "Private Sub CommandButton1_Click()".

First we check if the textbox contains a numeric value or is empty. If it isn't a numeric value, we delete the content, place the cursor in the textbox (the .SetFocus command) and keep the userform open.

If it is a numeric value, we convert it to the data type Double, insert the value in cell A1 on the active worksheet and close the userform.

We must convert to the data type Double, because the content of a textbox is always a String. If you don't convert it, things will go wrong, if you want to use the number in a calculation.

Private Sub CommandButton1_Click()
Dim dNumber As Double

With TextBox1
   If IsNumeric(.Text) = False Or Len(.Text) = 0 Then
      .Text = ""
      MsgBox "It must be a numeric value"
      .SetFocus
      Exit Sub
   Else
      'Convert the text (String) to the data type Double
      dNumber = CDbl(.Text)
   End If
End With

'Insert the number in cell A1
Range("A1").Value = dNumber

'Close the userform
Unload Me
End Sub

The smart way: The TextBox class

The code above works fine for a simple control, but if you have many textboxes on one or more userforms, and they all have to be checked the same way, it takes a lot of work to check them all.

The clever solution is to make a textbox class that will check all the textboxes on the fly, when the user types something. It may be that all letters must be upper case, or that the input must be numeric values.

The trick is to tell VBA that the textboxes on a userform are members of the textbox class, and when the user writes something in one of the textboxes, the event will automatically trigger a check by the textbox class' code.

It may sound a bit complicated, but you don't have to understand immediately - as long as it works, be happy!

Start by making a userform and put some textboxes on it. You must also add a command button that will close the userform when clicked.

Then you copy the following code and insert it at the very top of the userform's code. Note that I declare a public variable: "Public InputNbCol As Collection". Normally you would do that in a module, but to make things easier I do it here - after all it is just an example.

Option Explicit
Public InputNbCol As Collection

'The Initialize proceduren runs, when the userform opens
Private Sub UserForm_Initialize()
Dim InputNbEvt As clTextBoxClass
Dim ctl As control

On Error GoTo ErrorHandle

'Makes the collection that will contain our textbox classes.
Set InputNbCol = New Collection

'Now we loop through the userform's
control elements to find the textboxes.
For Each ctl In Me.Controls
   'If the control is a textbox
   If TypeOf ctl Is MSForms.TextBox Then
      'we make a new instance of the textbox class
      Set InputNbEvt = New clTextBoxClass
      'and sets it = this textbox
      Set InputNbEvt.InputTextBox = ctl
      'which we add to our collection, InputNbCol
      InputNbCol.Add InputNbEvt
   End If
Next

BeforeExit:
'As the class now "lives" as a member of our class collection,
'we can remove the original (last) instance to save memory.
Set InputNbEvt = Nothing
Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

'And here is the click procedure for our command button
Private Sub CommandButton1_Click()
'Runs when the user clicks the command button.

'Write some code here if you want any
'action before the userform closes.

'Now the collection of textbox classes is
'set = Nothing to free memory.
Set InputNbCol = Nothing

'Close the userform
Unload Me
End Sub

And now for the class module

Make a class module (in the VBA editor menu you select "Insert" and "Class Module"). In the class module's properties window you change the name to "clTextBoxClass". If the properties window isn't visible, select it in the menu "View" or press F4.

Having done that you copy the code below and paste into the class module. But first I had better explain what goes on in the class module.

In this example we accept nothing but numeric values, decimal separator (.) and minus (for negative values). This is controlled by the procedure:

Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)

which is called every time the user presses a key. We check the character's Ascii code and accept numeric values only (and "." and "-").

Of course you can check the same way if for instance you only accept upper case. At the bottom of the page you can see how that would look.

However the Keypress procedure only captures "normal" keys being pressed (ANSI characters), so if a user pastes a copied text into the textbox with CTRL+V, he can cheat us.

Therefore the class will also prevent the user from pasting text into a textbox. The way it does this is neither sophisticated nor elegant, but it works.

Private Sub InputTextBox_BeforeDropOrPaste

that captures attempts to paste something. Here we set a flag, "bPaste = True", that will make the procedure:

Private Sub InputTextBox_Change()

Here is the code to copy. Highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into the class module.

Option Explicit
Public WithEvents InputTextBox As MSForms.TextBox
Private mvarPaste As Boolean
Private mvarText As String

'****
Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)
'Allows nothing but numbers, dots and minus.
'Ascii 46 is a dot, Ascii 45 is minus. If you use comma
'as decimal separator, the Ascii code is 44.

Select Case KeyAscii
   Case 45 To 57
   Case Else
      KeyAscii = 0
End Select

'We save the text in a variable
sText = InputTextBox.Text

End Sub

'****
Private Sub InputTextBox_BeforeDropOrPaste _
(ByVal Cancel As MSForms.ReturnBoolean, _
ByVal Action As MSForms.fmAction, _
ByVal Data As MSForms.DataObject, _
ByVal X As Single, ByVal Y As Single, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)

'If the user is pasting something, we raise a flag
bPaste = True

End Sub

'****
Private Sub InputTextBox_Change()
'This procedure executes once the textbox' content has been changed.

With InputTextBox
'If the flag for paste is raised, we insert any old text.
   If bPaste Then
      bPaste = False
      .Text = sText
   End If
End With

End Sub


'Below are the class' properties. We could also declare them
'as variables on module level, however as we are playing with class
'modules in this example, properties are proper :-)
Property Get bPaste() As Boolean
   bPaste = mvarPaste
End Property
Property Let bPaste(ByVal vData As Boolean)
   mvarPaste = vData
End Property
Property Get sText() As String
   sText = mvarText
End Property
Property Let sText(ByVal vData As String)
   mvarText = vData
End Property

Allowing upper case only

In the example above we accepted nothing but numeric values. Here follows the Keypress procedure to use instead, if you want to disallow anything but upper case in your textboxes.

It is an open question, if anybody will find it useful, but it is just another example to give you an impression of the technique.

Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)
'Converts lower case to upper case.

Select Case KeyAscii
'Control charaters
Case 8 To 10, 13, 27
   KeyAscii = KeyAscii
'Upper case
Case 65 To 90, 194, 197, 198, 212, 216, 219
   KeyAscii = KeyAscii
'Convert lower case to upper case
Case 97 To 122, 226, 229, 230, 244, 248, 251
   KeyAscii = Asc(UCase$(Chr$(KeyAscii)))
'Drop everything else
Case Else
   KeyAscii = 0
End Select

'Save the text in a variable
sText = InputTextBox.Text

End Sub

Other events

With the textbox class you can capture and treat almost the same events as in the userform's textbox code. That is events like:

This is possible because we declared the following at the top of our class module:

Public WithEvents InputTextBox As MSForms.TextBox

where the important thing is the expression "WithEvents" - which can be described as "when something happens to our textbox".

For the same reason we cannot (like in the userform) capture things like "AfterUpdate" or "Exit", because in those situations we missed the train, so to say.