Run-Time Errors 380 and 381 generated when searching for a word in a Listbox through a textbox on a-CodePudding

I am new to Excel VBA. I wanted a code for searching for a word in a listbox that has data drawn from a worksheet. I came across a video on YouTube (Link: https://youtu.be/IuOYQASY3p4), which describes a code for running a search in a Listbox for a keyword typed into a Textbox on the same Userform. The code seemed perfect for my project. But I found it works perfectly for a data set of 6 columns, but when I change the code and corressponding control properties for a data set of 11 columns, the code throws up Run-Time Errors 380 and 381 in the two For... Next loops in the code. I am attaching an Excel file I prepared you experts here to review the code I am using. I would highly appreciate it if you would tell me where I am going wrong. I have indicated with comments where the errors occur in the code. I am using Excel 2013 on Windows 10.

My code:

Private Sub TextBox2_AfterUpdate()

    With Me.ListBox2
    .Clear
        For ColHead = 1 To 11
           .AddItem
           .List(0, ColHead - 1) = Sheet2.Cells(1, ColHead).Value  '<<< 'Run-Time Error 380, Invalid Property Value
        Next ColHead
        
    ' If I comment out the For... Next loop above the code runs up to the next For... Next loop below _
      and there it throws up run-time error 381. I have marked it there with a comment.

    ListRow = 1
    If IsDate(Me.TextBox2) Then
        FindVal = CDate(Me.TextBox2)
        ElseIf IsNumeric(Me.TextBox2) Then FindVal = Val(Me.TextBox2)
        Else
        FindVal = "*" & Me.TextBox2 & "*"
    End If
    LastRow = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
    For ShRow = 2 To LastRow
        FindRow = Application.WorksheetFunction.CountIf(Sheet2.Rows(ShRow).EntireRow, FindVal)
        If FindRow > 0 Then
            For ListCol = 1 To 11
                .AddItem
                .List(ListRow, ListCol - 1) = Sheet2.Cells(ShRow, ListCol).Value '<<< 'Run-Time Error 381, Invalid Property Value
            Next ListCol
        ListRow = ListRow   1
        End If
    Next ShRow
    End With

End Sub

Thank you for your help.

CodePudding user response：

There is a limit of 10 columns if you use unbound data sources for a listbox:

From the official documentation:

Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays all the available columns. For an unbound data source, there is a 10-column limit (0 to 9).

Update: There are two ways to fill a listbox:
The first is to assign a Range to the listbox (the property is called RowSouce). With that, you can have more than 10 columns, and you can have a header row. This is called "bound" data source (because the listbox data is bound to a range).
The second is to fill the list manually (as you do), by calling AddItem for every row you want to display. As the data is not bound to anything, this is called "unbound", and for whatever reason there are a max of 10 columns supported (0..9) and you can't have a header row.

The property BoundColumn defines which column is seen as "value". If you define 1 as boundColumn and check the value-property of the listbox (eg in a change event), the value of the first column of the selected row is used. This is true for bound and unbound lists.