Data validation

T

tkaplan

i have an excel form that i want that after the user enters data, when
he tries to exit text box it validates the data for him and if it's
valid the background changes to white.
i currently have the colors changing if there is any data in the txtbox
at all. i would like it to be that it needs to have two names in the
field - the director's first and last name. so i want it to check the
the first word is more than one letter, followed by a space, and then
at least two more letters.

i am putting the code in the txtDirector_Exit event.
i know the changing color code, i need to code to check the data in the
box.

thanks in advance
tkaplan
 
D

Dave Peterson

What version of excel are you using?

This relies on split which was added in xl2k.

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub txtDirector_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myStr As String
Dim mySplit As Variant
Dim ErrorWasFound As Boolean
Dim myErrMsg As String
Dim iCtr As Long

myStr = Application.Trim(Me.txtDirector.Value)
myErrMsg = ""

mySplit = Split(myStr, " ")

ErrorWasFound = False
If (UBound(mySplit) - LBound(mySplit) + 1) <> 2 Then
myErrMsg = "Not 2 Names!"
ErrorWasFound = True
End If

For iCtr = LBound(mySplit) To UBound(mySplit)
If Len(mySplit(iCtr)) < 2 Then
ErrorWasFound = True
myErrMsg = myErrMsg & vbLf & "Names too short"
Exit For
End If
Next iCtr

Me.Label1.Caption = myErrMsg

Cancel = ErrorWasFound


End Sub
Private Sub UserForm_Initialize()
Me.Label1.Caption = ""
End Sub


If you're using xl97, you can include this function that was written by Tom
Ogilvy:

Function Split97(sStr As String, sdelim As String) As Variant
'from Tom Ogilvy
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

Change this line:
mySplit = Split(myStr, " ")
to
mySplit = Split97(myStr, " ")
 
Top