VBA code to make multiple text boxes numeric entry only

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.

Private Sub Input1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
    KeyAscii = 0

End If

End Sub

Thanks in advance for your help

Jess

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

Public WithEvents Control As MSForms.TextBox

Private Sub Control_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
        If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
            KeyAscii = 0
        End If
End Sub

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

Dim TextBoxes() As Variant

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

Private Sub UserForm_Initialize()
        
    Dim TB As Object
    
        ReDim TextBoxes(1 To 167)
        
        For i = 1 To 167
            Set TB = New clsTextBox
            Set TB.Control = Me.Controls("Input" & i)
            Set TextBoxes(i) = TB
        Next i
        
End Sub

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

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

Set TB = New clsTextBox

To this...

Set TB = New Class1

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.

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

Private Sub UserForm_Initialize()
    
    Dim i       As Long
    Dim TB      As Object
    Dim cLoc    As Range
    Dim ws      As Worksheet
    
        ReDim TextBoxes(1 To 156)
        
        For i = 5 To 156
            Set TB = New Class1
            Set TB.Control = Me.Controls("Input" & i)
            Set TextBoxes(i) = TB
        Next i
    
    Set ws = Worksheets("Lookuplists")
    For Each cLoc In ws.Range("Dynamic_StaffList")
    With Me.Input4
        .AddItem cLoc.Value
    End With
    Next cLoc

    Dim DayCount As Integer

    DayCount = 1
     
    Do
        input1.AddItem "0" & DayCount
        DayCount = DayCount + 1
    Loop Until DayCount = 10
    
        Input2.AddItem "January" & MonthCount
        Input2.AddItem "Febuary" & MonthCount
        Input2.AddItem "March" & MonthCount
        Input2.AddItem "April" & MonthCount
        Input2.AddItem "May" & MonthCount
        Input2.AddItem "June" & MonthCount
        Input2.AddItem "July" & MonthCount
        Input2.AddItem "August" & MonthCount
        Input2.AddItem "September" & MonthCount
        Input2.AddItem "October" & MonthCount
        Input2.AddItem "November" & MonthCount
        Input2.AddItem "December" & MonthCount
    
        Input3.AddItem "2013" & YearCount
        Input3.AddItem "2014" & YearCount
        Input3.AddItem "2015" & YearCount
    
    Do
        input1.AddItem DayCount
        DayCount = DayCount + 1
    Loop Until DayCount = 32
    


End Sub