Hi Greg

(how's sunny adelaide this morning - did you get any sleep?)

how about:

Private Sub TextBox1_Change()
Dim ans

On Error Resume Next
ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B1:B100"), ans)
TextBox3.Text = Application.Index(Range("C1:C100"), ans)
If UCase(Application.Index(Range("F1:F100"), ans)) = "NO" Then _
MsgBox "Warning: This person is not financial!"
R = 1 'or whatever desired row variable
Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text)
Else
UserForm2.Show
End If
On Error GoTo 0

End Sub

---
cheers
JulieD


"Greg" wrote in message
...
Hi all I am using this code so when a person puts number into text box a
it
gives the details in textbox's 2&3. How can I get this code to look in
column "f" in this column I have a yes/no section. If the corresponding
number is NO I want a pop up to mention this person is not financial.

How do I change the code below to do this?


Private Sub TextBox1_Change()



Dim ans

On Error Resume Next
ans = Application.Match(CLng(TextBox1.Text), Range("A1:A100"), 0)
If Not IsError(ans) Then
TextBox2.Text = Application.Index(Range("B1:B100"), ans)
TextBox3.Text = Application.Index(Range("C1:C100"), ans)
R = 1 'or whatever desired row variable
Sheets("sheet5").Cells(R, 1).Value = UCase(TextBox1.Text)
Else
UserForm2.Show
End If
On Error GoTo 0

End Sub

Thanks again

Greg