Блог

Итак, я пытаюсь проверить несколько текстовых полей. Вот ситуация: некоторые из моих текстовых полей принимают только цифры, а некоторые принимают буквы с тире. Приведенные ниже коды проверяют только числа, но я не знаю, как я могу объединить их с буквами с тире.

Файл класса:

 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 " amp; 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
 

Кажется, что вы можете использовать тег, чтобы отличить, является ли он числовым типом или символьным типом, и выполнить событие с помощью этого тега.

Модуль класса

 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 " amp; 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