Here is a subroutine that should do what you want...

Sub GetPersonInfo(ID As String, TheName As String, Position As String)
Dim FileNum As Long, TotalFile As String, Info() As String, Data As String
Const PathAndFileName As String = "C:\Temp\TestFile.txt"
FileNum = FreeFile
Open PathAndFileName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Info = Split(vbNewLine & TotalFile, vbNewLine & ID & " ", 2)
If UBound(Info) 0 Then
Data = Trim(Split(Info(1), vbNewLine)(0))
Position = Mid(Data, InStrRev(Data, " ") + 1)
TheName = Left(Data, InStrRev(Data, " ") - 1)
Else
Position = "<<Invalid ID specified"
TheName = "<<Invalide ID specified"
End If
End Sub

Note... this is a **subroutine** not a macro; hence it must be called from
your own code (which could be a macro, another subroutine or even a
function). You supply the ID argument (which you have indicated would come
from TextBox1) and provide two String arguments to receive the information
you seek back from the subroutine. Here is an example of it being used...

Private Sub CommandButton1_Click()
Dim ID As String, Person As String, Job As String
ID = 1039
GetPersonInfo ID, Person, Job
MsgBox "ID: " & ID & vbLf & "Person: " & Person & vbLf & "Job: " & Job
End Sub

This example simply shows a MessageBox displaying the information for ID
#1039... in the program you suggested you needed this for, you would assign
the Person variable's contents to TextBox2 and the Job variable's contents
to TextBox3 and perform any other necessary code after that. Note that if an
invalid ID number is passed into the subroutine, the TheName and Position
arguments will be set to the text String "<<Invalid ID specified"... you
can, of course, handle that situation any way that you want inside the Else
block reserved for that code.

Rick Rothstein (MVP - Excel)




wrote in message
...

Hi All,
I have read through all post on net in this weekend about my problem,
however I could not get right answer. I have a text file which is
named as stafflist with user id numbers, name and positions. Let's say
as below
1039 Tony Adwards SUP-V
1277 John Philips DRGSM-V
1326 Ken Through DRGSM-V
.......

That is the file with 5000 users. I do not want to keep those user
details in excel file, because it takes a lot of space.Is there any
way to write a code to lookup value from that text file. Let's say
once enter in the id number( 1039 ) textbox1 textbox2 to display the
name and textbox3 display the position.
Thank you for the help
Baha