如何在用户表单列表框中显示过滤的行 [英] How to show filtered rows in userform listbox

问题描述

我在用户表单中有一个 Excel 工作表和一个列表框.

I have one Excel sheet and a listbox in a userform.

当我通过单击用户窗体上的按钮过滤工作表并更新列表框时，我会看到列表框中的所有行.我的意思是 listbox1 显示所有单元格(过滤器 + 无过滤器).

When I filter my sheet and update listbox by clicking on a button on my userform I see all rows in the listbox. I mean listbox1 show all cells (filter + no filter).

我更新列表框的代码:

Private Sub CommandButton1_Click()
    CommandButton10.Visible = True
    insertlist1.Visible = True
    ListBox1.Visible = True
    ListBox1.RowSource = "'NEWPRJ'!D7:D46"
End Sub

推荐答案

使用数组时，列表框标题消失...
因此，您可以尝试使用两个想法来解决问题:
1. 对表格进行排序，使过滤后的值排在最前面(在表格的表头下方)；
2.过滤表格；

When using arrays, the listbox header goes away...
So you could try to solve the problem using two ideas:
1. Sort the table, to make the filtered values come to top (under the header of the table);
2. Filter the table;

Private Sub fillListBox()
'lstGrade as the listbox component
Dim oTab As ListObject
Dim oRng As Range
    Set oTab = Sheets("Sheet1").ListObjects("MyTable")

    'remove any filter and then sort the data using the "SomeValue" to stick it on top of the table
    With oTab
        .Range.AutoFilter
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=Range("MyTable[Column3]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            CustomOrder:="SomeValue", _
            DataOption:=xlSortNormal
        With .Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

    'note that "SomeValue" is the same as in the sorted area above
    oTab.Range.AutoFilter 2, "SomeValue"

    '"save" the data into the new range object
    Set oRng = oTab.DataBodyRange.SpecialCells(xlCellTypeVisible)
    lstGrade.RowSource = oRng.Address
End Sub

这篇关于如何在用户表单列表框中显示过滤的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！