Excel macro or command button to search using a user form

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

Ryan H

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top