0

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?

T.M.
  • 9,436
  • 3
  • 33
  • 57
errorfree
  • 103
  • 1
  • 1
  • 8

2 Answers2

1

The column index of the listbox also starts at 0. The index number of additem should be 0, and you specified 15 at the end, then the number of columns becomes 16, so an error occurs because column 14 is exceeded.

It would be convenient to use an array.

Private Sub FindButton_Click()

    Dim Ws As Worksheet
    Dim vDB As Variant, vResult()
    Dim i As Long, j As Integer, n As Long
    Set Ws = Sheets("db")
    vDB = Ws.Range("a1").CurrentRegion
    For i = 1 To UBound(vDB, 1)
        If InStr(1, vDB(i, 2), FindDMC.Value, vbTextCompare) > 0 Then
            n = n + 1
            ReDim Preserve vResult(1 To 14, 1 To n)
            For j = 1 To 14
                vResult(j, n) = vDB(i, j)
            Next
        End If
    Next i
    With ListBoxResult
        .Clear
        .ColumnCount = 14
        .ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50;50;"
        If n Then
            If n = 1 Then
                .Column = vResult
            Else
                .List = WorksheetFunction.Transpose(vResult)
            End If
            
        End If
    End With
    
End Sub
Dy.Lee
  • 7,527
  • 1
  • 12
  • 14
  • Nice and quick as ever, upvoted. - Friendly hint: you can do **without transposing** by assigning results to the listbox'es **`.Column` property** without any further condition, simply via `If n Then .Column = vResult`. - Personally I'd `Redim` only 2 times (when starting to the possible boundary maximum and outside the loop a 2nd and last time). - FYI maybe interested in a similar question [here](https://stackoverflow.com/questions/64818906/search-via-textbox-to-auto-update-listbox-entries/65542359#65542359) where I referred an elder post of yours, too – T.M. Jan 03 '21 at 09:16
  • Nice shot! It working good. But how can I solve problem with precise finding. For example: if I have in column two similar value like "X1" and "X11" and the input value will "X1" then it find and display both results. – errorfree Jan 03 '21 at 12:49
  • @PawelU `If vDB(i, 2) = FindDMC.Value Then` for exact findings; if you want to compare case insensitively you'd have to compare both items converted via `LCase()` or `UCase()` :-) – T.M. Jan 03 '21 at 15:49
  • 1
    @T.M., Thank you. Now I know what you're talking about. However, you write the result array outside the loop, but it is more efficient to use dynamic arrays inside the loop. A common misconception is that specifying the array size in advance is faster, but in real tests, it is faster to assign a value by incrementing the array by one. – Dy.Lee Jan 04 '21 at 02:25
  • @Dy.Lee - thank you for replying, it's always a pleasure reading your helpful posts. - I'm glad having supported to improve the `.Column` assignment. - As for the second point, your hint is new for me and it would be worth testing with varying row/column parameters, possibly also if there's sort of compromise *redimming* in portions ... or do you have concrete test results availabe for greater data sets by chance? – T.M. Jan 04 '21 at 13:05
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
T.M.
  • 9,436
  • 3
  • 33
  • 57
  • Problem solved! Thanks T.M. this is good lesson for me. Thank you very much and I wish you all the best! – errorfree Jan 03 '21 at 16:19
  • @PawelU - Fine that the issue is solved and you learned, but I didn't intend that you accept this post as preferred answer & un-accepting his approach at the same time since Dy.Lee showed the way to go :-) – T.M. Jan 03 '21 at 16:24
  • Further reading regarding the 10 columns limitation [Populate listbox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440); using the array method instead of the `.AddItem` method overcomes this; sort of workaround is to combine both methods - c.f. [here](https://stackoverflow.com/questions/64818906/search-via-textbox-to-auto-update-listbox-entries?noredirect=1&lq=1) – T.M. Jan 03 '21 at 17:36