Creating Lookup User Form
 

Happy New Year to all of you!

I have an Excel file with a sheet containing information of students
(student name, student #, address, etc). I want to use that sheet to create
a user form for lookup.

For example, on the user form, I want to have a combo box for student #.
When I select a student # from the combo box, the rest of information
(student name, address, etc) of that student will automatically appear in the
respective text boxes.

Can someone help me with creating one?

Thanks.

Creating Lookup User Form
 

Suppose the data is in columns A-C of Sheet1, with #
in column B. Set the ComboBox's RowSource property
like this: Sheet1!B2:B6

Put the following code in the UserForm's code module:

Private Sub ComboBox1_Change()
Dim rng As Range
With ComboBox1
If .ListIndex = -1 Then
TextBox1 = ""
TextBox2 = ""
Else
Set rng = Range(.RowSource)(.ListIndex + 1)
TextBox1 = rng.Offset(0, -1)
TextBox2 = rng.Offset(0, 1)
End If
End With
End Sub

Hth,
Merjet

Creating Lookup User Form
 

Hi merjet,

Thanks for the code.

After I typed in your code and when I clicked on the command button from the
main form to go to the lookup form, I got an error "Path/File access error".
When I click on OK on that error message, I got another error message
"Run-time error '75': Could not find the specified object.". If I clicked on
the "Debug" button, it pointed me to the line "frmStudentNoLookup.Show" of
the command button that I clicked from the main form. Then I "Reset" the
VBA, and it crashed and I lost my entire VBAProject.

Do you know what caused to crash?

Below is my current set up for these lookup form:

Basically, I have a main form with command buttons to the lookup forms and a
"Close" command button to close the main form. The form's X buttons are
disable on both main form and lookup forms (to force the users to return to
the main form and use the "Close" button) with the code below:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub

On my lookup forms, I have a combox box and a few text boxes. The
ControlSource and RowSource of the combox box are referenced to the sheet as
"B2" and "B2:B2000", respectively.

On any lookup form, when I select a value from the combo box, the text boxes
will show the values associated with the combox box.


Below is my current code:


Private Sub cboStudentNo_Change()
Dim rng As Range
With cboStudentNo
If .ListIndex = -1 Then
txtStudentID = ""
txtStudentFName = ""
txtStudentLName = ""
Else
Set rng = Range(.RowSource)(.ListIndex + 1)
txtStudentID = rng.Offset(0, 1)
txtStudentFName = rng.Offset(0, 2)
txtStudentLName = rng.Offset(0, 3)
End If
End With
End Sub


Thanks.

"merjet" wrote:

Suppose the data is in columns A-C of Sheet1, with #
in column B. Set the ComboBox's RowSource property
like this: Sheet1!B2:B6

Put the following code in the UserForm's code module:

Private Sub ComboBox1_Change()
Dim rng As Range
With ComboBox1
If .ListIndex = -1 Then
TextBox1 = ""
TextBox2 = ""
Else
Set rng = Range(.RowSource)(.ListIndex + 1)
TextBox1 = rng.Offset(0, -1)
TextBox2 = rng.Offset(0, 1)
End If
End With
End Sub

Hth,
Merjet

Creating Lookup User Form
 

merjet,

Sorry about the long message previously posted.

I have figured out why I got an error. I got an error because I inserted a
cell reference in the ControlSource of combo box. When I removed it, I was
no longer receiving errors. However, I am no longer able to list my values
"student numbers" in the combo box, even though I have "B:B" in the
RowSource.

Do you know how I can list student numbers in the combo box? The student
numbers are in column B.

Thanks.

"merjet" wrote:

Suppose the data is in columns A-C of Sheet1, with #
in column B. Set the ComboBox's RowSource property
like this: Sheet1!B2:B6

Put the following code in the UserForm's code module:

Private Sub ComboBox1_Change()
Dim rng As Range
With ComboBox1
If .ListIndex = -1 Then
TextBox1 = ""
TextBox2 = ""
Else
Set rng = Range(.RowSource)(.ListIndex + 1)
TextBox1 = rng.Offset(0, -1)
TextBox2 = rng.Offset(0, 1)
End If
End With
End Sub

Hth,
Merjet