CODEWITHSUNDEEP
excelvbalistbox
Pherdindy
Pherdindy

Reputation: 1178

Listbox not showing the values that were populated in it using Listbox.List method

After running the Userform_Initialize() event, there would be nothing populated in the listbox as shown below:

enter image description here

There should be 11 columns populating the listbox based on the excel table below:

enter image description here

The code ran:

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

The properties of the listbox:

enter image description here

enter image description here

Upvotes: 1

Views: 2992

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You can populate each list row and then add the columns to it:

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

Note here is a nice guide how to work with list objects.

Upvotes: 2

Related Questions