Populating listbox via combobox on userform

[jacob1234]

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:

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

 

[gallen]

I haven't got the time to write the code for you but you need to perform a loop that utilises the .FindNext method of the range object. If you google it you will find many examples.

Essentially you store the first result's address in a separate variable then keep performing the .FindNext. If the address of the found cell isn't the same as the first one you have another cell with the same value.

 

[jacob1234]

Thanks Gallen, I have retried using below code:

Private Sub ComboBox1_Change()
Frame4.Visible = True
 
With Sheets("STUDENTLIST").Range("A2:A500")
    Set c = .Find(ComboBox1.Value, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            ListBox1.AddItem
            Set c = .FindNext(c)
            If c Is Nothing Then
                GoTo DoneFinding
            End If
            Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
DoneFinding:
End With



  With ListBox1
        
        .ColumnCount = 5
     
         
        .ColumnWidths = "0;50;50;0;0"
            .List(.ListCount - 1, 0) = c.Value
            .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
           .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
            .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
            .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            .List(.ListCount - 1, 5) = c.Offset(0, 5).Value
        End With

This now creates two rows in the listbox but only shows data in the second row, do you know how I could get the data from the first row also?

 

[gallen]

If you follow your code through, line by line, you should see your issue.

Everything needs to be within the loop. So every time you findnext, you add an item to your listbox. As it is now, you will only ever get the last one it finds as you only enter anything to your listbox once the loop as finished.