The first 4 characters can be any alphabet (A-Z) and the fifth one should be zero ( 0 ) or any numeric value in the textbox of user form.

[SamarthSalunkhe]

Hi All,

I am using the below code to restrict numbers only in the textbox of user form, it is working very well. just for knowledge purposes, I want to change restrictions like in the test box I want to allow the first 4 characters can be any alphabet (A-Z) and the fifth one should be zero ( 0 ) or any numeric value. Eg. : "ABCD0BD459"

Private Sub txtMobile_Change()
 'Mobile number Must be in Numbers only.
Dim x As Long
Dim a As String
a = "##########"

With txtMobile
    x = Len(.Text)
    If Not .Text Like Left(a, x) Then
          .Text = Left(.Text, x - 1): MsgBox "Mobile number Must be Numeric", vbExclamation, "Incorrect Mobile Number"
    End If
End With
End Sub

 

[Zot]

You can use something like this

If .Text Like "[A-Z][A-Z][A-Z][A-Z][0-9]*" Then

 

[Akuini]

There are 2 ways to validate a textbox entry:
1. validate it each times you type a character, as your code does by using Change event.
2. validate it after you finish typing in the textbox, you can validate it by using LostFocus event.

The second one is actually easier to code.
Any particular reason why you're using Change event?

 

[SamarthSalunkhe]

There are 2 ways to validate a textbox entry:
1. validate it each times you type a character, as your code does by using Change event.
2. validate it after you finish typing in the textbox, you can validate it by using LostFocus event.

The second one is actually easier to code.
Any particular reason why you're using Change event?

Hi Akuini,

I am using change to prevent it from copying past or mouse drop,

"validate it after you finish typing in the textbox, you can validate it by using LostFocus event."

can you please guide me to use lostfocus event, sorry but I'm beginner in VBA.

Thanks.

 

[Zot]

Using change event would be a problem I think since the macro will get executed after each character entry. You do not want to run macro until complete entry. You can use Enter so that the code get run once you press Enter.

 

[Akuini]

What are the complete validation criteria?
Does it have to be 10 characters ?

 

[SamarthSalunkhe]

What are the complete validation criteria?
Does it have to be 10 characters ?

Yes it contains 11 characters and the criteria are First 4 characters should be any Alphabet, the fifth one Zero ( 0 ) and the last 6 can be any Alphabet or any Number.

it is the Indian IFSC code, for Eg: SBIN0SN044D

 

[Akuini]

Try:

Private Sub TextBox1_LostFocus()

With TextBox1
    If .Value <> "" Then
        If Not .Value Like "[A-Z][A-Z][A-Z][A-Z]0[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]" Then
            .Activate
'            .Value = ""
             MsgBox "Wrong input", vbExclamation, "Incorrect Mobile Number"
        End If
    End If
End With

End Sub

if the text isn't valid when you move the focus out of the textbox then the focus will get back to the textbox along with the message.

 

[Akuini]

Sorry, I just realized that you're talking about textbox on a userform, so use this one:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With TextBox1
    If .Value <> "" Then
        If Not .Value Like "[A-Z][A-Z][A-Z][A-Z]0[A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9][A-Z0-9]" Then
'            .Value = ""
             MsgBox "Wrong input", vbExclamation, "Incorrect Mobile Number"
            Cancel = True

        End If
    End If
End With


End Sub

 

[SamarthSalunkhe]

Hi Akuini,

Thank you so much for your support.