Validation

[Dave]

Help for a form newbie.

I need to verify input to a text box in a form.
It must be exactly 6 characters.
First is always "p"
The next 5 are any number.

I know how to verify if it is all a number but not sure
how to do it for a combo of letter and numbers.

Any help would be appreciated. Sorry if this is a second
post. I posted yesterday but did not see it appear.

Thanks in advance

 

[Bernie Deitrick]

Dave,

You could use the change event of the textbox. The example below if for
Testbox1.

You would also need to check the length of the Textbox1.Text prior to
accepting further processing, say when the OK button is clicked.

Private Sub CommandButton1_Click()
If Len(TextBox1.Text) <> 6 Then Exit Sub
'Other code here

End Sub

HTH,
Bernie
MS Excel MVP

Private Sub TextBox1_Change()
Dim mytest As Double
If Len(TextBox1.Text) = 0 Then Exit Sub
If Len(TextBox1.Text) = 1 Then
If UCase(TextBox1.Text) = "P" Then
Exit Sub
Else
MsgBox "You have to start with a ""P"""
TextBox1.Text = ""
Exit Sub
End If
End If
If Len(TextBox1.Text) > 6 Then
TextBox1.Text = Left(TextBox1.Text, 6)
MsgBox "That's long enough!"
Exit Sub
End If

On Error GoTo Bad

mytest = CDbl(Mid(TextBox1.Text, 2, 6))
Exit Sub
Bad:
MsgBox "You need to enter a P followed by 5 digits!"
TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)

End Sub

 

[Dave]

Thanks very much Bernie, Works great!

-----Original Message-----
Dave,

You could use the change event of the textbox. The example below if for
Testbox1.

You would also need to check the length of the Textbox1.Text prior to
accepting further processing, say when the OK button is clicked.

Private Sub CommandButton1_Click()
If Len(TextBox1.Text) <> 6 Then Exit Sub
'Other code here

End Sub

HTH,
Bernie
MS Excel MVP

Private Sub TextBox1_Change()
Dim mytest As Double
If Len(TextBox1.Text) = 0 Then Exit Sub
If Len(TextBox1.Text) = 1 Then
If UCase(TextBox1.Text) = "P" Then
Exit Sub
Else
MsgBox "You have to start with a ""P"""
TextBox1.Text = ""
Exit Sub
End If
End If
If Len(TextBox1.Text) > 6 Then
TextBox1.Text = Left(TextBox1.Text, 6)
MsgBox "That's long enough!"
Exit Sub
End If

On Error GoTo Bad

mytest = CDbl(Mid(TextBox1.Text, 2, 6))
Exit Sub
Bad:
MsgBox "You need to enter a P followed by 5 digits!"
TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1)

End Sub




.