Run-time error '381': Could not set the List property. Invalid property array index

Question

As a single-column listbox, this works and brings back all the data I need, however, upon making this a multi-column listbox, it throws me the error "Run-time error '381': Could not set the List property. Invalid property array index" - see code line ListBox1.List(1, 0) = MyArray(i, 1)

Columns are correctly shown when I add the incorrect List Property and I've tried adding the List Property where I think it should be, but it shows the whole list again and replaces the 2nd row with one of the correct search results, the 1st row never changes.

Main code is from How to filter listbox values based on a Textbox value and I've used the comment by @xris23 to amend into the multi-column listbox thus far.

Please help!

Sub CommandButton1_Click()
    Dim i As Long, j As Long, rw As Long
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim rng As Range
    Dim MyArray As Variant
    Dim sPath As String
        
    sPath = "U:\GroupEmailDataCut.xlsx"
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    
    If xlApp Is Nothing Then
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = True
    End If
    
    Set xlBook = xlApp.Workbooks.Open(sPath)
    
    Set ws = xlBook.Sheets("GroupEmailDataCut")
    
    Set rng = ws.Range("A2:D" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)
    MyArray = rng
        
    With UserForm1.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
                
        If ws.Range("A" & ws.Rows.Count).End(xlUp).Row > 1 And Trim(UserForm1.TextBox1.Value) <> vbNullString Then
            MyArray = ws.Range("A2:D" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Value2
            For i = LBound(MyArray) To UBound(MyArray)
                For j = LBound(MyArray, 1) To UBound(MyArray, 1)
                    If InStr(1, MyArray(i, 1), Trim(UserForm1.TextBox1.Value), vbTextCompare) Then
                        UserForm1.ListBox1.AddItem
                        ListBox1.List(1, 0) = MyArray(i, 1) '<----Error is across ListBox1.List (MyArray is working as intended)
                        ListBox1.List(1, 1) = MyArray(i, 2)
                        ListBox1.List(1, 2) = MyArray(i, 3)
                        ListBox1.List(1, 3) = MyArray(i, 4)
                    End If
                Next
            Next
        End If
        .List = MyArray
    If UserForm1.ListBox1.ListCount = 1 Then UserForm1.ListBox1.Selected(0) = True
    End With
End Sub

Answer 1

There are several points to observe to make the command button event readable or even runnable:

• Don't address controls by referencing the Userform's default instance (returning the public members of that object), reference instead the current instance starting either with the Me. qualifier or simply by naming the userform control alone. Understand the Userform (class) code behind as sort of pattern which is blue-printed to the current instance(s).
• You are using .AddItem to fill a listbox with items (which is okay for e.g. 4 columns); consider, however that this method automatically builds 10 list columns, but not less or more; this can be avoided .. a) by assigning an entire 2-dim array to the .List (or .Column) property or .. b) by a workaround assigning an empty array plus immediate clearing which allows .AddItem also for greater column numbers (integrated in section 2a)
• .List indices are zero-based, i.e. they start with 0 whereas the datafield array with its row and column indices is one-based: .List(cnt, c - 1) = data(r, c) - See section 2b)
• Furthermore I separated the check for findings into a help function IsFound(), integrated list assignments into loops and tried to make code more readable by some other intermediate variable declarations.

Private Sub CommandButton1_Click()
'~~~~~~~~~~~~~~
'1) Define data
'~~~~~~~~~~~~~~
'1a)Define search string
    Dim srch As String:   srch = Trim(Me.TextBox1.Value)
'1b)Define data range
    Dim ws As Worksheet:  Set ws = Tabelle1
    Dim lastRow As Long:  lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    Dim rng     As Range: Set rng = ws.Range("A2:D" & lastRow)
'1c)Provide for possible exit
    If rng.Row < 1 Or srch = vbNullString Then Exit Sub
'1d)Get 1-based 2-dim datafield array
    Dim data() As Variant: data = rng.Value
'~~~~~~~~~~~~~~
'2) Fill ListBox
'~~~~~~~~~~~~~~
    With Me.ListBox1
    '2a)Set listbox properties & clear list
        .ColumnHeads = False
        .MultiSelect = fmMultiSelectMulti
        'Provide for ColumnCount > 10
        Dim ColCnt As Long: ColCnt = rng.Columns.Count
        .ColumnCount = ColCnt
        If ColCnt > 10 Then
            Dim tmp(): ReDim tmp(1 To 1, 1 To ColCnt)
            .List = tmp
        End If
        .Clear                                   ' clear the ListBox
    '2b)Loop through rows & add findings to list
        Dim r As Long, c As Long, cnt As Long
        For r = LBound(data) To UBound(data)     ' 1 ..
            If IsFound(srch, data, r) Then       ' << help function IsFound()
                .AddItem                         ' add item
                For c = LBound(data, 2) To UBound(data, 2)
                    .List(cnt, c - 1) = data(r, c) '<< .List is zero-based!
                Next
                cnt = cnt + 1                    ' increment counter
            End If
        Next
        If .ListCount = 1 Then .Selected(0) = True
    End With
End Sub

Help function IsFound()

Function IsFound(srch As String, arr, ByVal r As Long) As Boolean
'Purp.: check if search string is found in given 2-dim array row
    Dim c As Long           ' column index
    For c = LBound(arr, 2) To UBound(arr, 2)
        If InStr(1, arr(r, c), srch, vbTextCompare) Then
            IsFound = True  ' positive function result
            Exit For        ' one finding suffices
        End If
    Next
End Function

Further links

• You'll find an outstanding article giving you some insight at UserForm1.Show?
• Treating the different show modes you might be interested also in Destroy a modeless Userform instance properly