Copy >10 columns multicolumn listbox in Userform to array

Question

I tried several solutions from several forums but nothing seemed to help.

I am pretty sure that I am making some stupid mistake and the solution is right before my eyes, but I cannot make it work.

I need to fill a listbox in a user form with 12 columns.

As far as I know, for more than 10 columns, one can no longer use the .List(i) attribute but must use an array to populate it. My problem is that everytime I need to insert a new line into the listbox, I need to copy the items in the listbox to the array, redim the array to have one more line, "insert" the new data into this new line and, finally, copy the array into the listbox.

My code is as follows:

iLine = lbPlanPreview.ListCount
ReDim arr(iLine, lbPlanPreview.ColumnCount - 1)

If iLine > 0 Then
    ReDim arr(0 To iLine - 1, lbPlanPreview.ColumnCount - 1)

    'Array Lines
    For iCur = 0 To iLine - 1
        'Array Columns
        For iCurAux = 0 To lbPlanPreview.ColumnCount - 1
            arr(iCur, iCurAux) = Me.lbPlanPreview.ListIndex(iCur, iCurAux)
        Next iCurAux
    Next iCur
End If

It always gives a type mismatch error:

Any idea? Thanks.

Answer 1

Correct ListBox.List function

Think your last array assignment to restore prior data should be stated as follows and possibly be limited to lbPlanPreview.ListCount - 1:

'Array Lines
 For iCur = 0 To lbPlanPreview.ListCount - 1    ' possibly instead of: ILine - 1
    'Array Columns
    For iCurAux = 0 To lbPlanPreview.ColumnCount - 1
        arr(iCur, iCurAux) = Me.lbPlanPreview.List(iCur, iCurAux)
    Next iCurAux
 Next iCur