Number restriction in VBA UserForm

SamarthSalunkhe

Board Regular
Joined
Jun 14, 2021
Messages
103
Office Version
  1. 2016
Platform
  1. Windows
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?


VBA 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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
But I'm curious why it doesn't work.
so I write this 2712345678 when write P it pop up message
but when I write number insted of P .it writes the number like this 27123456789
 
Upvote 0
.it writes the number like this 27123456789
27123456789 is only 11 characters, the "P" should be the 12th character, so you should type 27123456789P
 
Upvote 0
should be the 12th character, so you should type 27123456789P
sorry this my mistak I write small letter not capital as your code
now it works .thanks so much .:)
 
Upvote 0
Putting an option at the beginning of the module may fix the P case not matching. Works with other string comparisons, not sure about "Like".

VBA Code:
Option Compare Text
 
Upvote 0
Ok, great. (y)
Hi Akuini,

Sorry to disturb you again, suppose If I want to give the command as 12th character should be an APLHA in UPPERCASE.

Actually, code is fulfilled my requirements just asking for knowledge.
 
Upvote 0

Forum statistics

Threads
1,223,943
Messages
6,175,551
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top