none
Single click event handler for multiple textbox text change event RRS feed

  • 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
    

    Wednesday, January 20, 2016 12:28 PM

All replies

  • 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
    
    


    • Proposed as answer by André Santo Wednesday, January 20, 2016 6:14 PM
    Wednesday, January 20, 2016 2:40 PM
    Moderator