maybe:

Private Sub TextBox1_Change()

Dim lngAns As Long

On Error Resume Next
'assuming Sheet1.Range("A1:A100") only have unique value
lngAns = CLng(Application.WorksheetFunction.Match(CLng(Text Box1.Text),
Sheet1.Range("A1:A100"), 0))
If Not IsError(ans) Then
TextBox2.Text = Sheet1.Cells(lngAns, "B").Value
TextBox3.Text = Sheet1.Cells(lngAns, "C").Value
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

"Greg" wrote:

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