Populate UserForm ListBox based on ComboBox Selection

[JaredArchuleta]

Hello all, I have the following UserForm and would like to only show ListBox results for the employee that is selected.

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

Sub AnalyzeEmployees()
    Dim DataTable As ListObject
    Set DataTable = ThisWorkbook.Sheets("Data").ListObjects("records")

    Set shData = ThisWorkbook.Sheets("Data")

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

    With EmployeeAnalysis.lbxEmployeeResults
        .Clear
        .ColumnCount = rng.Columns.Count

        myArray = rng

        .List = myArray

        .ColumnWidths = "90;100;100;100;50"
        .TopIndex = 0
    End With
    
    EmployeeAnalysis.Show
    
End Sub

I need help with the code for when I select a name, I only see results for the person selected.

The name of my combobox is: cbxEAName

Thank you all in advance

 

[JaredArchuleta]

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

 

[Domenic]

Edited

When only one record is found, you end with a 5-Row by 1-Column two-dimensional array. And when you transpose it, you actually end up with a one-dimensional array. As a result, when assigning a one-dimensional array to the List property of the listbox, it fills the column, not the row.

One possible solution would be to first count the number of records found, and then declare and allocate the exact storage space needed for your variable. This way, when only one record is found, you're left with a 1-Row by 5-Column two dimensional array, which you assign to the List property of the listbox. This is also more efficient, since it avoids having to resize your array with Redim Preserve each time a record is found, and it avoids having to transpose the array.

I have amended your macro accordingly...

Private Sub cbxEAName_Change()

    EmployeeAnalysis.lbxEmployeeResults.Clear
 
    Dim shData As Worksheet
    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 recordCount As Long
    recordCount = Application.CountIf(rng.Columns(1), Me.cbxEAName.Value)
 
    If recordCount = 0 Then Exit Sub
 
    Dim filteredData() As Variant
    ReDim filteredData(1 To recordCount, 1 To rng.Columns.Count)
 
    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
            For j = 1 To rng.Columns.Count
                filteredData(numRows, j) = rng.Cells(i, j).Value
            Next j
        End If
    Next i
 
    With EmployeeAnalysis.lbxEmployeeResults
        If numRows > 0 Then
            .ColumnCount = rng.Columns.Count
            .ColumnWidths = "90;100;100;100;50"
            .List = filteredData
            .TopIndex = 0
        End If
    End With

End Sub

Hope this helps!

 

[Domenic]

Correction, since we only need to count the names from the first column in order to get a record count, please replace...

recordCount = Application.CountIf(rng, Me.cbxEAName.Value)

with

recordCount = Application.CountIf(rng.Columns(1), Me.cbxEAName.Value)

Hope this helps!