Populate UserForm ListBox based on ComboBox Selection

Question

I have the following UserForm and would like to only show ListBox results for the employee that is selected. When I select the first name in the combobox, I get the correct results.

But when I select any other combox options, all of the data goes into the first column of my listbox.

What am I doing wrong?

Here is the current VBA code I am using to populate the list box:

Private Sub cbxEAName_Change()

Set shData = ThisWorkbook.Sheets("Data")

Dim rng As Range
Set rng = shData.Range("C2:G" & shData.Range("A" & shData.Rows.Count).End(xlUp).Row)

Dim filteredData() As Variant
Dim i As Long
Dim j As Long
Dim numRows As Long

numRows = 0
For i = 1 To rng.Rows.Count
    If rng.Cells(i, 1).Value = EmployeeAnalysis.cbxEAName.Value Then
        numRows = numRows + 1
        ReDim Preserve filteredData(1 To rng.Columns.Count, 1 To numRows)
        For j = 1 To rng.Columns.Count
            filteredData(j, numRows) = rng.Cells(i, j).Value
        Next j
    End If
Next i

With EmployeeAnalysis.lbxEmployeeResults
    .Clear
    .ColumnCount = rng.Columns.Count
    If numRows > 0 Then
        .List = Application.Transpose(filteredData)
    End If
    .ColumnWidths = "90;100;100;100;50"
    .TopIndex = 0
End With

End Sub

Answer 1

try

this will find the combobox value in column C then add the corresponding values in columns C-D-E-F-G to the listbox

Private Sub cbxEAName_Change()
Dim empfound As Range, firstAddress
    Dim shData As Worksheet

    Set shData = ThisWorkbook.Sheets("Data")

    Dim rng As Range
    Set rng = shData.Range("C2:C" & shData.Range("A" & shData.Rows.Count).End(xlUp).Row)

    With lbxEmployeeResults
        .Clear
        .ColumnCount = 5
        .ColumnWidths = "90;100;100;100;50"
        '.TopIndex = 0
        Set empfound = rng.Find(cbxEAName.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not empfound Is Nothing Then
            firstAddress = empfound.Address
            Do
                .AddItem shData.Cells(empfound.Row, "C").Value
                .List(.ListCount - 1, 1) = shData.Cells(empfound.Row, "D").Value
                .List(.ListCount - 1, 2) = shData.Cells(empfound.Row, "E").Value
                .List(.ListCount - 1, 3) = shData.Cells(empfound.Row, "F").Value
                .List(.ListCount - 1, 4) = shData.Cells(empfound.Row, "G").Value
                Set empfound = rng.FindNext(empfound)
            Loop While Not empfound Is Nothing And empfound.Address <> firstAddress
        End If
    End With
End Sub