Excel macro or command button to search using a user form

  • Thread starter Thread starter cvw
  • Start date Start date
C

cvw

I have a user form which shows info about each student. I want to be able to
enter a student's name in a textbox and have all the fields on the form
populated with the student information.
 
You need to be a lot more specific on what you are wanting else we have to
make a lot of assumptions.

I would recommend using a combobox instead of a textbox. This way you can
utilize the Change Event and have the students info. automatically filled in
the rest of your userform. I will assume you have a list of students in
Sheet1 in Col. A. Just set the RowSource property of the combobox to the
range of students, for example, Sheet1!A2:A100. Now I will assume the
students information is in Col. B thru Col. D. Use this code below. You
will have to tweak it to your application. It should get you started. Hope
this helps! If so, let me know, click "YES" below.

Private Sub ComboBox1_Change()

Dim Student As Range
Dim rw As Long

Set Student = Sheets("Sheet1").Range("A:A").Find(What:=ComboBox1.Text, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False)

If Not Student Is Nothing Then

rw = Student.Row
With Sheets("Sheet1")
Me.TextBox1 = .Cells(rw, "B").Value
Me.TextBox2 = .Cells(rw, "C").Value
Me.TextBox3 = .Cells(rw, "D").Value
'etc.
End With
Else
MsgBox "Student not found."
End If

End Sub
 
Back
Top