VBA从多ListObjects筛选填充ListBox及关联ListBox展示问题

Got it, let's break this down into two straightforward parts—first filling your first ListBox with all entries marked "Top" from multiple ListObjects, then linking that selection to show the full associated table in the second ListBox. Here's a step-by-step implementation:

This code will loop through all ListObjects in your workbook, filter for rows where the Position column equals "Top", and add those rows to your first ListBox. We'll also store the parent table name for each entry (to link to the second ListBox later).

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim colPosition As ListColumn
    Dim filteredRows As Range
    Dim targetRow As ListRow
    Dim listIndex As Integer
    Dim colIndex As Integer
    
    ' Reset ListBox1
    ListBox1.Clear
    ' Set column count to match your table structure (adjust as needed)
    ListBox1.ColumnCount = 4 ' Example: 3 visible columns + 1 hidden for table name
    ListBox1.ColumnHeads = True
    ' Hide the last column (where we'll store the table name)
    ListBox1.ColumnWidths = "100;100;100;0"
    
    ' Loop through every worksheet
    For Each ws In ThisWorkbook.Worksheets
        ' Loop through every ListObject in the worksheet
        For Each tbl In ws.ListObjects
            ' Check if the table has a "Position" column
            On Error Resume Next
            Set colPosition = tbl.ListColumns("Position")
            On Error GoTo 0
            
            If Not colPosition Is Nothing Then
                ' Filter the table for "Top" values
                tbl.Range.AutoFilter Field:=colPosition.Index, Criteria1:="Top"
                
                ' Grab visible data rows (skip header)
                On Error Resume Next
                Set filteredRows = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
                On Error GoTo 0
                
                If Not filteredRows Is Nothing Then
                    ' Add each filtered row to ListBox1
                    For Each targetRow In tbl.ListRows
                        If targetRow.Range.EntireRow.Hidden = False Then
                            ListBox1.AddItem
                            listIndex = ListBox1.ListCount - 1
                            ' Add table column values to ListBox
                            For colIndex = 1 To tbl.ListColumns.Count
                                ListBox1.List(listIndex, colIndex - 1) = targetRow.Range.Cells(1, colIndex).Value
                            Next colIndex
                            ' Store the table name in the hidden column
                            ListBox1.List(listIndex, tbl.ListColumns.Count) = tbl.Name
                        End If
                    Next targetRow
                End If
                
                ' Remove filter from the table
                tbl.AutoFilter.ShowAllData
            End If
        Next tbl
    Next ws
End Sub

Key Notes for Step 1: #

• Adjust ColumnCount and ColumnWidths to match your actual table columns. The hidden column holds the parent table name for later reference.
• The code skips hidden rows (from the filter) to only add valid "Top" entries.
• We restore the table filter after processing to keep your data clean.

When you select an item in ListBox1, this code will pull the stored table name, find that ListObject, and load all its rows into ListBox2.

Private Sub ListBox1_Click()
    Dim selectedTableName As String
    Dim tbl As ListObject
    Dim ws As Worksheet
    Dim rowIndex As Integer
    Dim colIndex As Integer
    
    ' Reset ListBox2
    ListBox2.Clear
    
    ' Exit if no item is selected
    If ListBox1.ListIndex = -1 Then Exit Sub
    
    ' Get the stored table name from the hidden column
    selectedTableName = ListBox1.List(ListBox1.ListIndex, ListBox1.ColumnCount - 1)
    
    ' Find the matching ListObject across all worksheets
    For Each ws In ThisWorkbook.Worksheets
        On Error Resume Next
        Set tbl = ws.ListObjects(selectedTableName)
        On Error GoTo 0
        
        If Not tbl Is Nothing Then
            ' Configure ListBox2 to match the table
            ListBox2.ColumnCount = tbl.ListColumns.Count
            ListBox2.ColumnHeads = True
            ListBox2.ColumnWidths = ListBox1.ColumnWidths ' Match first ListBox's visible widths
            
            ' Add all rows from the table to ListBox2
            If Not tbl.DataBodyRange Is Nothing Then
                For Each targetRow In tbl.ListRows
                    ListBox2.AddItem
                    rowIndex = ListBox2.ListCount - 1
                    For colIndex = 1 To tbl.ListColumns.Count
                        ListBox2.List(rowIndex, colIndex - 1) = targetRow.Range.Cells(1, colIndex).Value
                    Next colIndex
                Next targetRow
            End If
            Exit For ' Stop searching once we find the table
        End If
    Next ws
End Sub

Key Notes for Step 2: #

• The code uses the hidden column value from ListBox1 to locate the exact ListObject associated with the selected entry.
• ListBox2 inherits column settings from ListBox1 for consistency, but you can adjust this if needed.
• We exit the worksheet loop as soon as we find the target table to save processing time.

内容的提问来源于stack exchange，提问作者smartini