Enter numerical data in text boxes in userform using class module

We learnt earlier how to enter numerical data in a text box. If somebody entered an alphabet, a message would be given out that a non-numerical data entry in a text box had been attempted and the focus would be brought back to the text-box to make a numerical entry only. You can watch the training video on this topic also on YouTube. Now how could we enter numerical data in multiple text-boxes on a user-form. Would we need to write macro code for each of the text-boxes? That would be tedious and also error prone. In such a case we use a class module. In the class module we would define our multiple text-boxes as a group. Next we would write the code for this group to accept only numerical values via the keyboard. In a similar fashion to inserting a module we would insert a class module. This class module would be given a default name called ‘Class1’ and the code in the Class1 module would look like this:

Public WithEvents TextGroup As MSForms.TextBox

Private Sub TextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
‘numbers 0 to 9
Case 48 To 57
Case Else
KeyAscii = 0
MsgBox “You entered a non-numeric character.”, vbCritical, “Please enter numbers only!”
End Select
End Sub

How can you imagine a class? A class is like a template or blueprint for a user-defined object. This user-defined object has its properties, methods and events. So our class is a group of text-boxes and it reacts to the keypress events.

In the next step we create a user-form with the necessary number of text boxes and other controls. Next we create an ‘instance’ of our Class1 by using the keyword ‘New’ by double-clicking on our user-form and writing the following line of VBA code:
Dim textBoxes(1 To 4) As New Class1

In the ‘Initialize’ event when the form opens we check for the number of text-boxes and set each of the text-boxes as members of our TextGroup class with the following macro code:
Private Sub UserForm_Initialize()
Dim intTextBox As Integer
For intTextBox = 1 To 4
Set textBoxes(intTextBox).TextGroup = Controls(“TextBox” & intTextBox)
Next intTextBox
End Sub

Our coding process is complete. You’ll agree that this is a more powerful way of coding than writing VBA code for each text-box.

Watch the training video below to quickly understand how to use a class module to allow us to enter numerical data in multiple text-boxes on a user-form in Excel:

Download a sample file for practice:

Further reading:
Introduction to Classes

3 thoughts on “Enter numerical data in text boxes in userform using class module

  1. Hub van Gulick

    Dear people
    I implemented what you showed in: https://www.exceltrainingvideos.com/enter-numerical-data-in-text-boxes-in-userform-using-class-module/

    It works great and realy saves a lot of typing. Thanks for that.
    After that I tried to hookup a second function to that class , to verify the Values of the same
    -already declared- textboxes, like this

    Private Sub TextGroup_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox (“Mssg for the moment”)
    End Sub

    The implemented “TextGroup_Keypress” function is stil working fine.
    However, the added “TextGroup_Exit” function doesn’t trigger the msgBox at all, when the textbox is exit.

    Please can you put me in the right direction?

    Thanks in advance.

    Best regards,

    Hub van Gulick

  2. Nirmal Singh

    I enter data to a sheet through Userform but my numerical data get stored on the sheet as text data with error marking. I try to format the cells to numerical values but without success. What should I do.

  3. Nirmal Singh

    I have been able to sort out the problem myself by adding .value to the Textbox (= TextBox4.Value). Thanks I know you will be of help whenever needed.
    Thanks & Regards
    Nirmal Singh


Leave a Reply

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