Hi All,
I have a userform that brings data back from a worksheet and populates a listbox, but I only want to populate the textbox based on the variable shown in a combobox on the userform.
For example Users submit the following data from the userform into the worksheet:
Class
First Name
Surname
Year level
Student ID
I then want the listbox to pull through all rows of data where the combobox or in this case Class is equal to what is in Column A
Currently I have this code:
But I have noticed that it only pulls through the first row that matches the combobox - Could anyone please assist?
Much appreciated
Jacob
I have a userform that brings data back from a worksheet and populates a listbox, but I only want to populate the textbox based on the variable shown in a combobox on the userform.
For example Users submit the following data from the userform into the worksheet:
Class
First Name
Surname
Year level
Student ID
I then want the listbox to pull through all rows of data where the combobox or in this case Class is equal to what is in Column A
Currently I have this code:
Code:
Private Sub ComboBox1_Change()
Frame4.Visible = True
Dim rngToSearch As Range
Dim rngToFind As Range
Dim valToFind As Variant
Dim arrClearList()
valToFind = ComboBox1.Value 'Edit ComboBox1 to your ComboBox name
With Worksheets("STUDENTLIST")
Set rngToSearch = .Columns("A")
End With
Set rngToFind = rngToSearch.Find(What:=valToFind, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngToFind Is Nothing Then
'Call ClearList(Me.ListBox1) 'Optional to clear existing list
ListBox1.AddItem
With ListBox1
.ColumnCount = 5
'Set column widths
.ColumnWidths = "0;50;50;0;0"
.List(.ListCount - 1, 0) = rngToFind.Value 'ID Col A
.List(.ListCount - 1, 1) = rngToFind.Offset(0, 1).Value 'Agent Name Col B
.List(.ListCount - 1, 2) = rngToFind.Offset(0, 2).Value 'Address Col D
.List(.ListCount - 1, 3) = rngToFind.Offset(0, 3).Value 'Organization Col E
.List(.ListCount - 1, 4) = rngToFind.Offset(0, 4).Value 'Phone Col F
.List(.ListCount - 1, 5) = rngToFind.Offset(0, 5).Value 'Email col G
End With
But I have noticed that it only pulls through the first row that matches the combobox - Could anyone please assist?
Much appreciated
Jacob