Textbox Input Validation

[Dheepak]

Hi,

I have a text box which will get the product code of each product. All the product will have 10 digit and first 3 digit will be letters. I want to know how to validate the input entered in the text box is matching the above parameter.

For Example, the product code will be ABC1234567. If i enter in different format like two letters and 5 numbers or totally 11 digits means it should throw an error.

Hope I'll get reply and you have understood my concern. Thanks in advance.

Regards,
Dheepak

 

[royUK]

Which kind of TextBox?

 

[Dheepak]

Text box in VBA user form not in worksheet..

 

[jtdewhurst]

Here is the error check code. First it checks if the length is 10 characters, then checks if the last 7 digits make a number.

Err = 0
    If Len(Text.Value) <> 10 Then
        r = MsgBox("The product code you have entered is not 10 characters", , "Invalid Product Code")
        Err = 1
    ElseIf IsNumeric(Right(Text.Value, 7)) = False Then
        r = MsgBox("Invalid Product Code", , "Invalid Product Code")
        Err = 1
    End If

This is written as if the textbox were on a userform. If it is then you would put in an

If err=1 then
              exit sub
          end if

or

If err=0 then
                 'rest of code here
          end if

 

[jtdewhurst]

Sorry roy didn't mean to jump you, I was in excel working on the code, and didn't see you had replied, when I came with my solution.

 

[Dheepak]

Thanks a lot jt!!!!!!! it was the one which i expected. Thank you once again.

Best Regards,
Dheepak

 

[royUK]

jtdewhurst

No pronlem

 

[Rick Rothstein]

Thanks a lot jt!!!!!!! it was the one which i expected. Thank you once again.

Just so you know, the code jtdewhurst is not very robust given it should make sure the user typed in a valid product code. For example, it will permit the following "shaped" values (and a lot more) to be entered into the TextBox (I highlighted the problem parts in red)...

&*@1234567
ABC1234E56
ABC1234D56
ABC$123456
ABC&H12345
ABC123456$

I believe the following code will be more bullet-proof...

  If Not TextBox1.Text Like "[A-Z[B][COLOR="#FF0000"]a-z[/COLOR][/B]][A-Z[B][COLOR="#FF0000"]a-z[/COLOR][/B]][A-Z[B][COLOR="#FF0000"]a-z[/COLOR][/B]]#######" Then
    MsgBox "The Product Code you entered is not valid!", vbCritical, "Invalid Product Code"
    Exit Sub
  End If
  '
  ' Rest of code goes here
  '

Note... as written, the code will permit upper and lower case letters to be accepted... if you need the entries to be upper case letters only then remove the red highlighted "a-z" characters.

 

[jtdewhurst]

Thanks for that post Rick.
"Like" is a good one to file away for the future. I'd never heard of it before.