rETRIEVING DATA FROM EXCEL WORKSHEET TO CONTROLS IN USERFORM

[Guest]

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...

 

[Bob Flanagan]

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

 

[Guest]

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!