Number restriction in VBA UserForm

[SamarthSalunkhe]

Hello Everyone,

I'm using the below code for restriction in UserForm as the user must enter a number only.

but on the advanced level, I want the first 2 digits should start with 27 or 99.

is it possible to add this restriction in the below code?

Private Sub txtTIN_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case Len(Me.txtTIN.Text)
    Case 0
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "First Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 1
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Second Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
       
        End If
     Case 2
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Third Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 3
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Fourth Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 4
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Fifth Digit should be Numeric", vbExclamation, "Incorrect TAN"
               
        End If
     Case 5
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Sixth Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 6
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Seventh Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 7
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Eighth Digit should be Numeric", vbExclamation, "Incorrect TAN"
       
        End If
     Case 8
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Ninth Digit should be Numeric", vbExclamation, "Incorrect TAN"
           
        End If
     Case 9
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Tenth Digit should be Numeric", vbExclamation, "Incorrect TAN"
           
        End If
     Case 10
        If (KeyAscii > 47 And KeyAscii < 58) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 0
            MsgBox "Eleventh Digit should be Numeric", vbExclamation, "Incorrect TAN"
           
        End If
     Case 11
        If (KeyAscii > 64 And KeyAscii < 91) Or KeyAscii = 8 Then
            KeyAscii = KeyAscii
        Else
            KeyAscii = 80
            MsgBox "Twelfth Digit Must be { P } in UPPERCASE Only", vbExclamation, "Incorrect TAN"
           
           
    End If
End Select

End Sub

Thank you,
Samarth

 

[Akuini]

Hi, @SamarthSalunkhe
Try:

Private Sub TextBox1_Change()
' start with "27" or "99" & followed by unspecified digit
Dim x As Long
Dim a As String, b As String
a = "27"
b = "99"
    With TextBox1
        x = Len(.Text)
        Select Case x
            Case 1 To 2
                If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then Beep: .Text = Left(.Text, x - 1)
            Case Is > 2
                If Not .Text Like Left(a & WorksheetFunction.Rept("#", x - 2), x) And Not .Text Like _
                Left(b & WorksheetFunction.Rept("#", x - 2), x) Then Beep: .Text = Left(.Text, x - 1)
        End Select
    End With
End Sub

 

[Akuini]

MsgBox "Twelfth Digit Must be { P } in UPPERCASE Only", vbExclamation, "Incorrect TAN"

I just realized that you want to include letter "P" as the 12th char.
I also just realized that I made my previous code too complicated.

Private Sub TextBox1_Change()
' start with "27" or "99" & followed by 9 digit & "P"
Dim x As Long
Dim a As String, b As String
a = "27#########P"
b = "99#########P"
With TextBox1
    x = Len(.Text)
    If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then
          .Text = Left(.Text, x - 1): Beep
    End If
End With
End Sub

 

[SamarthSalunkhe]

I just realized that you want to include letter "P" as the 12th char.
I also just realized that I made my previous code too complicated.

Private Sub TextBox1_Change()
' start with "27" or "99" & followed by 9 digit & "P"
Dim x As Long
Dim a As String, b As String
a = "27#########P"
b = "99#########P"
With TextBox1
    x = Len(.Text)
    If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then
          .Text = Left(.Text, x - 1): Beep
    End If
End With
End Sub

Hello Akuini,

Thank you so much for your solution, It is working very well.

 

[abdelfattah]

@Akuini why you don't use for dimension as variant not string you have numbers and letters . why use as string .

I tested your code I can't write the letter P in the end as your code . if I'm wrong please correct me. I would just understand

thanks

 

[Akuini]

Thank you so much for your solution, It is working very well.

Glad it works.
If you want a message box, how about a more generic message like this:

Private Sub TextBox1_Change()
' start with "27" or "99" & followed 9 digit & "P"
Dim x As Long
Dim a As String, b As String
a = "27#########P"
b = "99#########P"
With TextBox1
    x = Len(.Text)
    If x = 0 Then Exit Sub
    If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then
        tx = "The format must be (x is any digit):" & vbLf & _
        "27xxxxxxxxxP OR 99xxxxxxxxxP"
        MsgBox tx, vbExclamation, "Incorrect TAN"
        .Text = Left(.Text, x - 1)
    End If
End With
End Sub

 

[Akuini]

why you don't use for dimension as variant not string you have numbers and letters . why use as string .

In this article:

Like operator

Office VBA reference topic

docs.microsoft.com

it says:
pattern: Required; any string expression conforming to the pattern-matching conventions described in Remarks.

The pattern is a string even if it has number in it. So I use string variable not variant, but variant should work too.

I tested your code I can't write the letter P in the end as your code

Not sure why that happens. Try a simpler pattern, like:
"2#P"
see if it works.

 

[Gokhan Aycan]

Just curious, if 12th char is always/must be a P, why bother with entering it in the first place?

 

[SamarthSalunkhe]

Glad it works.
If you want a message box, how about a more generic message like this:

Private Sub TextBox1_Change()
' start with "27" or "99" & followed 9 digit & "P"
Dim x As Long
Dim a As String, b As String
a = "27#########P"
b = "99#########P"
With TextBox1
    x = Len(.Text)
    If x = 0 Then Exit Sub
    If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then
        tx = "The format must be (x is any digit):" & vbLf & _
        "27xxxxxxxxxP OR 99xxxxxxxxxP"
        MsgBox tx, vbExclamation, "Incorrect TAN"
        .Text = Left(.Text, x - 1)
    End If
End With
End Sub

Hi Akuini,

I have used MsgBox in place of Beep, and that one also works perfectly.

Thank you ?

Private Sub txtTIN_Change()
' start with "27" or "99" & followed by 9 digit & "P"
Dim x As Long
Dim a As String, b As String
a = "27#########P"
b = "99#########P"
With txtTIN
    x = Len(.Text)
    If Not .Text Like Left(a, x) And Not .Text Like Left(b, x) Then
          .Text = Left(.Text, x - 1): MsgBox "First 2 Digit of TIN must be 27 or 99", vbExclamation, "Incorrect TAN"
    End If
End With
End Sub

 

[abdelfattah]

The pattern is a string even if it has number in it. So I use string variable not variant, but variant should work too.

thanks for clarification

Not sure why that happens. Try a simpler pattern, like:
"2#P"
see if it works.

unfortunately doesn't work . anyway thanks for your time