rETRIEVING DATA FROM EXCEL WORKSHEET TO CONTROLS IN USERFORM
 

hi! I have a userform with a texbox control where a user can input an ID no.
what i want is that when the length of textbox = 4 it will automatically
search that number in a worksheet STUDENT PROFILE which has the ff. fields:
ID no., lname, fname, mname, and addr. if that number is found its
corresponding lname, fname, mname, and addr. will be displayed in the
texboxes in that userform such as txtlname, txtfname, txtmname, and txtaddr.
I hope you'll help me. Thanks in advance...

rETRIEVING DATA FROM EXCEL WORKSHEET TO CONTROLS IN USERFORM
 

Rather than getting fancy and having the macro launch automatically when the
ID length reaches 4, just add a button that runs a search and fill macro.
You can record a macro to get the basics of the find command. Then, tailor
it a bit. The following will give you some ideas:

Dim cell As Range
Set cell = Nothing
With Workbooks("my workbook.xls").Sheets("Sheet1")
Set cell = .Columns("A:A").Find(What:="1234", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cell Is Nothing Then
userform1.Textbox1.Text = .Cells(cell.Row, 2).Value
'additional code to populate form
Else
MsgBox "No match found"
End If
end with

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Chinx21" . wrote in message
...
hi! I have a userform with a texbox control where a user can input an ID
no.
what i want is that when the length of textbox = 4 it will automatically
search that number in a worksheet STUDENT PROFILE which has the ff.
fields:
ID no., lname, fname, mname, and addr. if that number is found its
corresponding lname, fname, mname, and addr. will be displayed in the
texboxes in that userform such as txtlname, txtfname, txtmname, and
txtaddr.
I hope you'll help me. Thanks in advance...

rETRIEVING DATA FROM EXCEL WORKSHEET TO CONTROLS IN USERFORM
 

Set cell = .Columns("A:A").Find(What:="1234", After:=ActiveCell,
LookIn:=xlFormulas, _

can i use the (What:=txtidnum.text), in the Find(What:="1234",
thanks!

"Bob Flanagan" wrote:

Rather than getting fancy and having the macro launch automatically when the
ID length reaches 4, just add a button that runs a search and fill macro.
You can record a macro to get the basics of the find command. Then, tailor
it a bit. The following will give you some ideas:

Dim cell As Range
Set cell = Nothing
With Workbooks("my workbook.xls").Sheets("Sheet1")
Set cell = .Columns("A:A").Find(What:="1234", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not cell Is Nothing Then
userform1.Textbox1.Text = .Cells(cell.Row, 2).Value
'additional code to populate form
Else
MsgBox "No match found"
End If
end with

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Chinx21" . wrote in message
...
hi! I have a userform with a texbox control where a user can input an ID
no.
what i want is that when the length of textbox = 4 it will automatically
search that number in a worksheet STUDENT PROFILE which has the ff.
fields:
ID no., lname, fname, mname, and addr. if that number is found its
corresponding lname, fname, mname, and addr. will be displayed in the
texboxes in that userform such as txtlname, txtfname, txtmname, and
txtaddr.
I hope you'll help me. Thanks in advance...