May 27, 2015

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. Dear people
    I implemented what you showed in:

    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. 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. 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

Comments are closed.