Textbox data validation

[Jomathr]

I usually have to check Textbox against number or date wich is quite easy in VBA, but now I am faced with something I can't figure out letters and numbers

I have a textbox wich should contain data in the format LL0000, so 2 letters and 4 numbers but I am not quite sure how to validate it using VBA.
I'll have to use:


If InStr("/\? * - # + [ ]", Chr(KeyAscii)) Then KeyAscii = 0

to prevent symbol from being entered and already limited the number of character to 6, that much I know

Any help or pointers will help thank you!

[JKwan]

Try this out

[Jomathr]

ok the link you sent me does prevent the special character from being put in the textbox but it doesn't do the validtion I need, I need to check that what is in the checkbox is something like LL0000 or PJ01234. it must not accept something like 123456 or ABCDEF. I need something like mask in access where I can check each character to see if it's a number or a letter.

[Jomathr]

ok I think I figured it out, at least for the numeric part:



For j = 3 To 6
If Not IsNumeric(Mid(Me.TxtProjet.Value, i, 1)) Then
MsgBox ("wrong input"), vbExclamation
Cancel = True
Exit Sub
End If
Next j


I'll let you know if it works

[mancubus]

maybe something like this



Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With CreateObject("VBScript.RegExp")
.Pattern = "[A-Z]{2}[0-9]{4}"
If Not .test(TextBox1.Value) Then
MsgBox "Invalid input. Please enter text in ""AB1234"" (two capital letters and 4 numbers) format!"
Cancel = True
TextBox1.Value = ""
TextBox1.SetFocus
End If
End With
End Sub

[mikerickson]

Try this

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not (TextBox1.Text Like "[A-Za-z][A-Za-z]####")
If Cancel Then MsgBox "Please enter two letters followed by four numbers"
End Sub

[Jomathr]

Tried both mikerickson and mancubus solutions and they both work great!

I went with mikerickson's solution as it is a bit shorter and file size is an issue for what I am working on

Thank you very much for the answers and sorry for the late reply!