Multiple columns in ListBox (Userform) VBA

Question

I have a problem with displaying multiple columns in a ListBox in my UserForm. Everything is working until my numbers of column is max 10.

My code:

Private Sub FindButton_Click()
    ListBoxResult.Clear
    ListBoxResult.ColumnCount = 14
    Dim RowNum As Long
    RowNum = 1
    Do Until Sheets("db").Cells(RowNum, 1).Value = ""
        If InStr(1, Sheets("db").Cells(RowNum, 2).Value, FindDMC.Value, vbTextCompare) > 0 Then
            On Error GoTo next1
            ListBoxResult.AddItem Sheets("db").Cells(RowNum, 1).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 2) = Sheets("db").Cells(RowNum, 2).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 3) = Sheets("db").Cells(RowNum, 3).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 4) = Sheets("db").Cells(RowNum, 4).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 5) = Sheets("db").Cells(RowNum, 5).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 6) = Sheets("db").Cells(RowNum, 6).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 7) = Sheets("db").Cells(RowNum, 7).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 8) = Sheets("db").Cells(RowNum, 8).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 9) = Sheets("db").Cells(RowNum, 9).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 10) = Sheets("db").Cells(RowNum, 10).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 11) = Sheets("db").Cells(RowNum, 11).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 12) = Sheets("db").Cells(RowNum, 12).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 13) = Sheets("db").Cells(RowNum, 13).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 14) = Sheets("db").Cells(RowNum, 14).Value
            ListBoxResult.List(ListBoxResult.ListCount - 1, 15) = Sheets("db").Cells(RowNum, 15).Value
        End If
next1:
        RowNum = RowNum + 1
    Loop
End Sub

ListBoxResult.ColumnCount and properties is 14, also Column widths is ok. After runing my code the failure code is Run-time error '380': Could not set the List property. Invalid property value. At first, I was thinking that maybe ListBoxes have limits for columns, but I found ListBoxes with 60 columns on the Internet.

I am trying also this, but still doesn't work:

Private Sub Browser_RMA_Initialize()
 
ListBoxResult.RowSource = "db!a1:z1"
ListBoxResult.ColumnCount = 14
ListBoxResult.ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;"
ListBoxResult.ColumnHeads = True
 
End Sub

Could you support me, please?

Answer 1

Assigning to .Columnproperty avoids transposing

As late addition to @Dy.Lee 's valid and already accepted array approach (see my comment), I demonstrate a way how to avoid both repeated redimming [4] and transposing [5]:

Option Explicit                                  ' declaration head of UserForm code module

Private Sub FindButton_Click()
    '[0] where to search
    Const SearchCol As Long = 2                  ' get search items from 2nd column
    '[1] define data set
    Dim data As Variant
    data = Tabelle1.Range("A1").CurrentRegion    ' << change to your project's sheet Code(Name)
    Dim ii As Long: ii = UBound(data, 1)         ' row count
    Dim jj As Long: jj = UBound(data, 2)         ' column count
    '[2] provide for sufficient result rows (array with converted row : columns order)
    Dim results() As Variant
    ReDim Preserve results(1 To jj, 1 To ii)    ' redim up to maximum row count ii
    '[3] assign filtered data
    Dim i As Long, j As Integer, n As Long
    For i = 1 To ii
        If InStr(1, data(i, SearchCol), FindDMC.Value, vbTextCompare) > 0 Then
    ''  If data(i, SearchCol) = FindDMC.Value Then      ' exact findings
            n = n + 1
            For j = 1 To jj
                results(j, n) = data(i, j)
            Next
        End If
    Next i
    '[4] fill listbox with results
    With ListBoxResult
        .Clear
        .ColumnCount = 14
        .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;"
        If n Then
            '[4] redimension only a 2nd time (& last time)
            ReDim Preserve results(1 To jj, 1 To n)
            '[5] assign results to listbox'es .Column property
            .Column = results       ' << .Column property avoids unnecessary transposing
        End If
    End With
End Sub