Hastily thrown together sample file attached for you to see one way of accomplishing this (still not the best solution in my opinion).
Code used is below. It will obviously need amending so sheet and range names fit with your file.
Private Sub TextBox1_Change()
With TextBox1
If Len(.Value) = 4 Then
If Application.CountIf(ThisWorkbook.Sheets("Sheet1").Range("A:A"), .Value) = 0 Then
MsgBox "Invalid input", , ""
.Value = ""
Else
MsgBox "Valid input", , ""
End If
End If
End With
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
Select Case Len(.Value)
Case Is <= 2
If KeyAscii < 65 Or KeyAscii > 90 Then KeyAscii = 0
Case Is = 3
If KeyAscii < 48 Or KeyAscii > 57 Then KeyAscii = 0
Case Else
KeyAscii = 0
End Select
End With
End Sub
BSB
Bookmarks