Excel macro or command button to search using a user form

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.
0
Utf
2/9/2010 7:10:04 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1441 Views

Similar Articles

[PageSpeed] 7

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

-- 
Cheers,
Ryan


"cvw" wrote:

> 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.
0
Utf
2/9/2010 8:02:01 PM
Reply:

Similar Artilces: