excel - 列表框未显示使用 Listbox.List 方法填充其中的值

Question

运行Userform_Initialize()事件后，列表框中不会填充任何内容，如下所示：

根据下面的 excel 表，应该有 11 列填充列表框：

代码运行：

Private Sub UserForm_Initialize()

Dim Total_rows_FoilProfile As Long
Dim row As Range, i As Long

Total_rows_FoilProfile = TotalRowsCount(ThisWorkbook.Name, "Foil Profile", "tblFoilProfile")

ReDim MyArr(0 To Total_rows_FoilProfile - 1)

For Each row In ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").Range.SpecialCells(xlCellTypeVisible).Rows
    MyArr(i) = row.Value
    i = i + 1
Next row

lbxFoilInfoDisplay.List = MyArr

frmFoilPanel.Show

列表框的属性：

标签： excelvbalistbox

Answer 1

您可以填充每个列表行，然后向其中添加列：

Option Explicit

Private Sub UserForm_Initialize()
    Dim tblFoilProfile As ListObject
    Set tblFoilProfile = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile")

    Dim i As Long

    lbxFoilInfoDisplay.Clear

    Dim iListRow As Range
    For Each iListRow In tblFoilProfile.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
        With Me.lbxFoilInfoDisplay
            .AddItem iListRow.Cells(1, 1).Value 'add first value (column 1)

            Dim iCol As Long
            For iCol = 2 To iListRow.Columns.Count 'add all other columns to that row
                .list(i, iCol) = iListRow.Cells(1, iCol).Value '.Value for unformatted value or .Text to show it in the same format as in the cell
            Next iCol
            i = i + 1
        End With
    Next iListRow
End Sub

注意这里是一个很好的指南如何使用列表对象。