VBA code to make multiple text boxes numeric entry only

[JAL_0110]

Hello

Im very new to VBA coding and just looking for some help with short cuts.

I have a large userform used to record productivity data in my workplace. I have 167 text boxes (named Input1 up to Input167) and I want them to only be able to enter in numbers.


I have the following code which works fine, but I really dont want to have to repeat it 167 times. Can someone help me with some code so that I can cover all 167 text boxes at the one time.

Please Login or Register  to view this content.

Thanks in advance for your help

Jess

[Leith Ross]

Hello JAL_0110,

Welcome to the Forum!

This can be done easily using a technique known as "Subclassing". This allows for objects to be grouped together and share common event routines.

Before you proceed, have your workbook open and the VB Editor activated. To activate the VB Editor, press the keys ALT+F11.

Adding the Class Module and Code

1. To activate the VB Editor, press the keys ALT+F11.
2. Copy the Claas Code below using Ctrl+C.
3. Add a new Class Module to your VBA project. Press the keys [b]ALT+I[/] to display the Insert menu. Press C key.
4. Paste the code into the module using Ctrl+V.

Class Module Code

Please Login or Register  to view this content.

Additional UserForm Code
Add the code below to the very top of your UserForm...

Please Login or Register  to view this content.

Add the code below to the UserForm_Initialize() Event Module...

Please Login or Register  to view this content.

If you have any problems, post your workbook and I will fix any problems you are having.

[JAL_0110]

Hi Leith Ross

Thanks so much for your assistance. I followed your instructions but it came up with an error in Private Sub UserForm_Initialize():


Compile Error: User-Defined Type Not Defined
(on Set TB = New clsTextBox line)


I have attached the file. As you will see when you open it, there are a number of userforms being constructed. Please go to AddProductivityData userform.

Thanks Again for your help. It is greatly appreciated.

vsa productivity userform TEST.xlsm

[Leith Ross]

Hello JAL_0110,

Sorry about that, I forgot to edit that line after testing the code on my machine. It is a simple fix. The attached file has the macro change.

Change the line below
from this...

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

[JAL_0110]

Hello

Was getting a

Run-time error '13'
Type mismatch

(as i had put first textbox as input1, where first actual textbox was input5)

I have amended and is running like a dream.


Thanks so much for your help.

[Leith Ross]

Hello JAL_011,

I discovered that error after I posted the first fix. This error is happening because you are referencing TextBoxes that are not on the UerForm. Change the loop to start at 5 instead of 1 in the Initialize event and it will work. I just checked it to be sure.

Working UserForm_Initialize Code

Please Login or Register  to view this content.