Single click event handler for multiple textbox text change event

Question

Hello,

I have designed one userform in vba with 15 textboxes. On textbox change event, i want to give limitation to text value as textbox value does not exceed 400 and below 0, if the values will below 0 and above 400, it shows message popup as "Value out of range". My problem i want to add this code to each textbox change event, so i want to do this on single click event to handle multiple textbox text change event.

For reference, attached herewith screenshot of application and code too.

Please tell me the solution.

Thanks & Regards

Rushali

Private Sub CommandButton1_Click()
If TB1.Text > 400 Or TB1.Text < 0 Then
   MsgBox ("Value out of range")
Else
   TB1 = ""
End If
End Sub

Answer 1

Add a class module and name it "clsTextBox". Add the following into a userform and the class as indicated and run the form

' userform code
Private mArrClsTBX(1 To 50) As clsTextBox ' change 50 to # of textboxes

Private Sub UserForm_Initialize()
Dim r As Long, c As Long
Dim i As Long
Dim ctl As Control

    For c = 1 To 5
        For r = 1 To 10
            Set ctl = Me.Controls.Add("Forms.TextBox.1", "ob_" & r & "_" & c, True)
            ctl.Left = 9 + (c - 1) * 90
            ctl.Top = 30 + (r - 1) * 18
            ctl.Width = 81
            ctl.Height = 15
            
            i = i + 1
            Set mArrClsTBX(i) = New clsTextBox
            Set mArrClsTBX(i).pTbx = ctl
        Next
    Next
    Me.Height = 30 + r * 18 + 9
    Me.Width = 9 + (c - 1) * 90 + 9

'' If the texboxes are created at design time uncomment and use the following instead of the above

'    For Each ctl In Me.Controls
'        If TypeName(ctl) = "TextBox" Then
'            i = i + 1
'            Set mArrClsTBX(i) = New Class1
'            Set mArrClsTBX(i).pTbx = ctl
'        End If
'    Next

End Sub
'''''''''''''''''''''''''''

' clsTextBox code

Public WithEvents pTbx As MSForms.TextBox

Private Sub pTbx_Change()
Dim v
    If Len(pTbx.Text) = 0 Then Exit Sub
    
    v = Val(pTbx.Text)

    If CStr(v) <> pTbx.Text Then
        bFlag = True
    ElseIf v <> CLng(v) Then
        bFlag = True
    ElseIf v < 0 Or v > 400 Then
        bFlag = True
    End If

    If bFlag Then
        MsgBox "Enter a value between 0 and 400"
        pTbx.Text = ""
    End If

End Sub