excel 基于组合框选择填充UserForm列表框

rqdpfwrv 于 2023-05-08 发布在其他

关注(0)|答案(1)|浏览(132)

我有下面的用户表单，并希望只显示所选员工的列表框结果。当我在组合框中选择第一个名字时，我会得到正确的结果。

但是当我选择任何其他组合框选项时，所有数据都进入列表框的第一列。

我做错了什么？
下面是我用来填充列表框的当前VBA代码：

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

结束接头

excel

来源：https://stackoverflow.com/questions/76150532/populate-userform-listbox-based-on-combobox-selection

1条答案

按热度按时间

ymdaylpp1#

尝试
这将找到列C中的组合框值，然后将列C-D-E-F-G中的相应值添加到列表框中

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

赞(0）回复(0）举报 2023-05-08

我来回答

excel 基于组合框选择填充UserForm列表框

1条答案

相关问题

热门标签

最新问答