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]

No only 12th Character should be any alphabets in UPPERCASE.

Try:

Private Sub TextBox1_Change()
' start with "27" or "99" & followed by 9 digit &  a capital letter
Dim x As Long, n As Long
Dim a As String, b As String
a = "27#########[A-Z]"
b = "99#########[A-Z]"
With TextBox1
    x = Len(.Text)
    n = x: If x > 11 Then n = 16
    If Not .Text Like Left(a, n) And Not .Text Like Left(b, n) Then
          .Text = Left(.Text, x - 1): Beep
    End If
End With
End Sub

 

[SamarthSalunkhe]

w

Try:

Private Sub TextBox1_Change()
' start with "27" or "99" & followed by 9 digit &  a capital letter
Dim x As Long, n As Long
Dim a As String, b As String
a = "27#########[A-Z]"
b = "99#########[A-Z]"
With TextBox1
    x = Len(.Text)
    n = x: If x > 11 Then n = 16
    If Not .Text Like Left(a, n) And Not .Text Like Left(b, n) Then
          .Text = Left(.Text, x - 1): Beep
    End If
End With
End Sub

Wow Man, you are amazing,

Thank you again to clear my dought.

 

[Akuini]

You're welcome.