VBA Excel - Textbox Validation

Question

So I'm trying to validate multiple textboxes. Here's the situation, some of my textboxes only accept numbers only while some accept letters with a dash. The codes below validate numbers only, but I don't know how can I combine them with the letters with a dash.

Class file:

Private WithEvents tb As MSForms.TextBox   'note the "WithEvents"

Sub Init(tbox As Object)
    Set tb = tbox 'assigns the textbox to the "tb" global
End Sub

'Event handler works as in a form (you should get choices for "tb" in the
'  drop-downs at the top of the class module)
Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If KeyAscii >= 48 And KeyAscii <= 57 Then
            Debug.Print tb.Name, "number"
            tb.MaxLength = 3
    Else
        MsgBox "The value should be in number only!", vbOKOnly + vbCritical, "Error"
        Debug.Print tb.Name, "other"
        KeyAscii = 0
    End If
    
End Sub

Userform file:

Private colTB As Collectione

Private Sub UserForm_Activate()
    Dim c As Object
    Set colTB = New Collection
    'loop all controls in the frame
    For Each c In Me.Frame3.Controls
        'look for text boxes
        If TypeName(c) = "TextBox" Then
            Debug.Print "setting up " & c.Name
            colTB.Add TbHandler(c) ' create and store an instance of your class
        End If
    Next c
End Sub

Private Function TbHandler(tb As Object) As clsTxt
    Dim o As New clsTxt
    o.Init tb
    Set TbHandler = o
End Function

Validation for letters with a dash:

If (KeyAscii < 65 Or KeyAscii > 90) And (KeyAscii < 97 Or KeyAscii > 122) And KeyAscii <> 45 Then
            MsgBox "The value should be in letters only!", vbOKOnly + vbCritical, "Error"
            Debug.Print tb.Name, "other"
            KeyAscii = 0
    Else
        Debug.Print tb.Name, "letter"
End If

Dy.Lee · Accepted Answer · 2021-02-02 02:33:04Z

1

It seems that you can use a tag to distinguish whether it is a numeric type or a character type, and execute an event by this tag.

Class Module

Private WithEvents tb As MSForms.TextBox   'note the "WithEvents"

Sub Init(tbox As Object, s As String)
    Set tb = tbox 'assigns the textbox to the "tb" global
    tb.Tag = s
End Sub

'Event handler works as in a form (you should get choices for "tb" in the
'  drop-downs at the top of the class module)
Private Sub tb_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    If tb.Tag = "num" Then
        If KeyAscii >= 48 And KeyAscii <= 57 Then
                Debug.Print tb.Name, "number"
                tb.MaxLength = 3
        Else
            MsgBox "The value should be in number only!", vbOKOnly + vbCritical, "Error"
            Debug.Print tb.Name, "other"
            KeyAscii = 0
        End If
    Else
        If (KeyAscii < 65 Or KeyAscii > 90) And (KeyAscii < 97 Or KeyAscii > 122) And KeyAscii <> 45 Then
                MsgBox "The value should be in letters only!", vbOKOnly + vbCritical, "Error"
                Debug.Print tb.Name, "other"
                KeyAscii = 0
        Else
            Debug.Print tb.Name, "letter"
        End If
    End If
End Sub

Userform

Private colTB As Collection

Private Sub UserForm_Activate()
    Dim c As Object
    Set colTB = New Collection
    'loop all controls in the frame
    For Each c In Me.Frame3.Controls
        'look for text boxes
        If TypeName(c) = "TextBox" Then
            Debug.Print "setting up " & c.Name
            n = n + 1
            If n = 1 Then
                colTB.Add TbHandler(c, "num") ' create and store an instance of your class
            Else
                colTB.Add TbHandler(c, "string")
            End If
        End If
    Next c
    
    
End Sub

Private Function TbHandler(tb As Object, s As String) As clsTxt
    Dim o As New clsTxt
    o.Init tb, s
    Set TbHandler = o
End Function

answered Feb 2, 2021 at 2:33

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cjvdg Over a year ago

Thank you so much! It worked! But can I ask if how does it identify the textbox with the number only? Is this the line If n = 1 Then If n = 1 Then colTB.Add TbHandler(c, "num") that identify it?

Dy.Lee Over a year ago

@cjvdg, If the textbox is created in order from the top, you can use it like that.

Collectives™ on Stack Overflow

VBA Excel - Textbox Validation

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related