Manipulate a listbox of 11 columns

Question

I've got a listbox of 11 columns. When I try to add data to one of the columns, I get an error.

ListBox1.Column(10, j) = shtG.Cells(k, 13)

I don't understand why this happens, the listbox on the userform has a ColumnCount of 11.

The error I'm getting:

"Run-time error 380: Unable to set Column property. Invalid property value."

The value of the selected cell is "Group 16".

More info:

Code:

'adding this doesn't help
ListBox1.Clear
ListBox1.ColumnCount = 20

    While shtG.Cells(k, 1) <> ""
        If 'some long working condition Then

            frmTP.ListBox1.AddItem (shtG.Cells(k, kolID))
            frmTP.ListBox1.Column(1, j) = shtG.Cells(k, kolVnm) & strSpace & shtG.Cells(k, kolTV) & strSpace & shtG.Cells(k, kolAnm)
            frmTP.ListBox1.Column(2, j) = shtG.Cells(k, 5)
            frmTP.ListBox1.Column(3, j) = shtG.Cells(k, 6)
            frmTP.ListBox1.Column(4, j) = shtG.Cells(k, 7)
            frmTP.ListBox1.Column(5, j) = shtG.Cells(k, 8)
            frmTP.ListBox1.Column(6, j) = shtG.Cells(k, 9)
            frmTP.ListBox1.Column(7, j) = shtG.Cells(k, 10)
            frmTP.ListBox1.Column(8, j) = shtG.Cells(k, 11)
            frmTP.ListBox1.Column(9, j) = shtG.Cells(k, 12)
            frmTP.ListBox1.Column(10, j) = shtG.Cells(k, 13)
            j = j + 1
        End If
        k = k + 1
    Wend

You can only use 10 columns if you use `.AddItem`. You need to put the data into an array and then assign that to the `.List` property of the control. — Rory, Dec 04 '15 at 13:37
What's in shtG.Cells(k, 13) ? What's weird about that cell? If you comment out all the other assignments , does that one work or does it still break? — nicomp, Dec 04 '15 at 13:37
see this [SO question](http://stackoverflow.com/questions/11213962/vba-listbox-multicolumn-add). Answer was not marked, but it works. — Scott Holtzman, Dec 04 '15 at 13:40
Rory: I don't really understand what you mean. Can you maybe give me an example? Or do you just mean I should use .List instead of .Column? DelmerNicholson: There's nothing weird about the cell, it's just a string that says "Group 16" ScottHoltzman: Do they just mean I should use .List instead of .Column? — Grafit, Dec 04 '15 at 13:45
@Grafit - Yes, I think so. I tested it and worked very well :) — Scott Holtzman, Dec 04 '15 at 16:03
possible duplicate of [Adding items in a Listbox with multiple columns](http://stackoverflow.com/questions/6973287/adding-items-in-a-listbox-with-multiple-columns) — Scott Holtzman, Dec 04 '15 at 16:09

score 3 · Accepted Answer · answered Dec 04 '15 at 14:10

This is the sort of thing I mean (you could improve performance by loading the sheet data into an array to begin and processing that, and not resizing the array so often, but it would distract from the key idea here!):

Dim vData()
j = 0
While shtG.Cells(k, 1) <> ""
    If 'some long working condition Then
        ReDim Preserve vData(0 To 10, 0 To j)
        vData(0, j) = shtG.Cells(k, kolID).Value
        vData(1, j) = shtG.Cells(k, kolVnm) & strSpace & shtG.Cells(k, kolTV) & strSpace & shtG.Cells(k, kolAnm)
        vData(2, j) = shtG.Cells(k, 5)
        vData(3, j) = shtG.Cells(k, 6)
        vData(4, j) = shtG.Cells(k, 7)
        vData(5, j) = shtG.Cells(k, 8)
        vData(6, j) = shtG.Cells(k, 9)
        vData(7, j) = shtG.Cells(k, 10)
        vData(8, j) = shtG.Cells(k, 11)
        vData(9, j) = shtG.Cells(k, 12)
        vData(10, j) = shtG.Cells(k, 13)
        j = j + 1
    End If
Wend
frmTP.ListBox1.Column = vData

Excuse me for my late reaction. This works like a charm except for one thing. If my vData is empty I'm getting the same `runtime error 380` as before. I tried checking if my array is empty with: `If Len(Join(vdata)) <> 0 Then`, but with that statement I'm getting `runtime error 5: Invalid procedure call or argument`. — Grafit, Dec 11 '15 at 10:38

Manipulate a listbox of 11 columns

1 Answers1