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

Leave a Reply

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