I have an UserForm with some TextBoxes which individuals entries Id like to
validate before moving to the next Textbox. I want to force the user to make
a valid entry (non blank and right format) before moving to the next.
Should I place the code in the Form module??

Here is what I have for the first textbox which matches the entry to an
existing record
Sub Find_The_Name()
Set ws = ActiveSheet
Set UnitList = ws.Range("a:a")
With UnitList
Set rFound = .Find(What:=UnitNo, _
After:=Range("A1"), LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rFound Is Nothing Then
x = rFound.Address
CurrentTenant = ws.Range(x).Offset(0, 3).Text
Else
MsgBox "Couldn't find the unit number"
CurrentTenant = ""
Exit Sub
End If
End With
End Sub

Sub Find_Name01()
UnitNo = UserForm1.txtUnit01.Text
If UnitNo = "" Then
MsgBox "Make avalid entry"
Else
Call Find_The_Name
UserForm1.txtName01.Text = CurrentTenant
End If
End Sub

Thank you

Hi LuisE

You can use the Exit event of the text box

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Trim(TextBox1.Text) = "" Then Cancel = True
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"LuisE" wrote in message ...
I have an UserForm with some TextBoxes which individuals entries Id like to
validate before moving to the next Textbox. I want to force the user to make
a valid entry (non blank and right format) before moving to the next.
Should I place the code in the Form module??

Here is what I have for the first textbox which matches the entry to an
existing record
Sub Find_The_Name()
Set ws = ActiveSheet
Set UnitList = ws.Range("a:a")
With UnitList
Set rFound = .Find(What:=UnitNo, _
After:=Range("A1"), LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rFound Is Nothing Then
x = rFound.Address
CurrentTenant = ws.Range(x).Offset(0, 3).Text
Else
MsgBox "Couldn't find the unit number"
CurrentTenant = ""
Exit Sub
End If
End With
End Sub

Sub Find_Name01()
UnitNo = UserForm1.txtUnit01.Text
If UnitNo = "" Then
MsgBox "Make avalid entry"
Else
Call Find_The_Name
UserForm1.txtName01.Text = CurrentTenant
End If
End Sub

Thank you

The best practice is to minimize code placed in the user form module.
If you are directly accessing the controls then the code belongs in the form.
Other code should go in a standard module.
In your case, place Find_The_Name in a standard module and
call it from the form module passing it the UnitNo string...
I have changed Find_The_Name to a function and revised some of the code...

'In the Form module...
Sub Find_Name01()
Dim UnitNo As String
UnitNo = UserForm1.txtUnit01.Text
If UnitNo = "" Then
MsgBox "Make a valid entry"
Else
UserForm1.txtName01.Text = Find_The_Name(UnitNo)
End If
End Sub

'In a standard module
Function Find_The_Name(ByRef UnitN As String) As String
Dim CurrentTenant As String
Dim UnitList As Range
Dim rFound As Range
Set UnitList = ActiveSheet.Range("a:a")
Set rFound = UnitList.Find(What:=UnitN, _
After:=Range("A1"), LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not rFound Is Nothing Then
CurrentTenant = rFound.Offset(0, 3).Text
Else
CurrentTenant = "Couldn't find the unit number"
End If
Find_The_Name = CurrentTenant
End Function
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"LuisE"
wrote in message
I have an UserForm with some TextBoxes which individuals entries Id like to
validate before moving to the next Textbox. I want to force the user to make
a valid entry (non blank and right format) before moving to the next.
Should I place the code in the Form module??

Here is what I have for the first textbox which matches the entry to an
existing record
Sub Find_The_Name()
Set ws = ActiveSheet
Set UnitList = ws.Range("a:a")
With UnitList
Set rFound = .Find(What:=UnitNo, _
After:=Range("A1"), LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rFound Is Nothing Then
x = rFound.Address
CurrentTenant = ws.Range(x).Offset(0, 3).Text
Else
MsgBox "Couldn't find the unit number"
CurrentTenant = ""
Exit Sub
End If
End With
End Sub

Sub Find_Name01()
UnitNo = UserForm1.txtUnit01.Text
If UnitNo = "" Then
MsgBox "Make avalid entry"
Else
Call Find_The_Name
UserForm1.txtName01.Text = CurrentTenant
End If
End Sub

Thank you

Thanks. Now I'm facing a different problem.
I was able to prevent the user from no making a valid entry but if I doesn't
make and entry at all and want to leave the form without making any changes,
which is legit, it won't let close the form because of the cancel event =
true
Here is what I have

'''' Data validation
Private Sub txtUnit01_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.txtUnit01
If Len(.Text) = 0 Then
MsgBox "Please enter a valid unit number in this box"
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
Else
Call Find_Name01Misc
End If
End With
End Sub

'''Exit button
Private Sub cmdExit_Click()
Cancel = False
If MsgBox("This action will close the form and any " _
& vbCrLf & "unposted rent will be lost" & vbCrLf & _
"Do you really want to exit? ", vbYesNo + vbCritical) = vbNo
Then
Exit Sub
Else
Unload Me
Worksheets("dashboard").Activate
End If
End Sub

Thanks againg


"LuisE" wrote:

I have an UserForm with some TextBoxes which individuals entries Id like to
validate before moving to the next Textbox. I want to force the user to make
a valid entry (non blank and right format) before moving to the next.
Should I place the code in the Form module??

Here is what I have for the first textbox which matches the entry to an
existing record
Sub Find_The_Name()
Set ws = ActiveSheet
Set UnitList = ws.Range("a:a")
With UnitList
Set rFound = .Find(What:=UnitNo, _
After:=Range("A1"), LookIn:=xlValues, _
Lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If Not rFound Is Nothing Then
x = rFound.Address
CurrentTenant = ws.Range(x).Offset(0, 3).Text
Else
MsgBox "Couldn't find the unit number"
CurrentTenant = ""
Exit Sub
End If
End With
End Sub

Sub Find_Name01()
UnitNo = UserForm1.txtUnit01.Text
If UnitNo = "" Then
MsgBox "Make avalid entry"
Else
Call Find_The_Name
UserForm1.txtName01.Text = CurrentTenant
End If
End Sub

Thank you