excel - VBA Excel - 文本框验证

Question

所以我正在尝试验证多个文本框。情况是这样，我的一些文本框只接受 号码 仅当有些人接受 带破折号的字母 .下面的代码仅验证数字，但我不知道如何将它们与带有破折号的字母结合起来。

类文件:

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

用户表单文件:

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

验证带有破折号的字母:

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

Answer 1

好像可以用一个标签来区分是数字类型还是字符类型，通过这个标签执行一个事件。
类模块

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

用户表单

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