Add a row with more than 10 columns to a ListBox?

[onmyway365]

Hi guys,

i hope some one can help me. i am really stuck.

I have posted this elsewhere, but as I am struggling to get a working solution, I am also posting here in the hopes of a breakthrough. I will re-link etc. (http://www.excelforum.com/excel-prog...a-listbox.html)

Detail:
i have 12 controls/fields used for capturing data via a UserForm, updating a ListBox that is part of the same UserForm.
However, the problem lies in this: I can only capture 10 columns to the ListBox. I get an error every time the code loops from 10 to the 11th column.

Error: 380: Could not set List property, Invalid Property value.

My code is very simple:

Private Sub cmdAdd_Click()


        Dim arrCtrls As Variant
        Dim i As Long
        Dim LastRowIndex As Long
        Dim currentReference As RowReference


arrCtrls = Array(TextBox1, TextBox2, TextBox3, TextBox4, TextBox5, TextBox6, TextBox22, TextBox23, TextBox24, TextBox25, TextBox26, TextBox27)


            With ListBox1
                .ColumnCount = UBound(arrCtrls) + 2
                .AddItem
                           
                For i = 0 To UBound(arrCtrls) - 1
                    .List(.ListCount - 1, i) = arrCtrls(i).Value
                Next i
   
End With


End Sub

Thank you in advance!

 

[RoryA]

If you use AddItem you are limited to 10 columns. You need to create and populate an array and assign that to the List property of the control and then you can use more columns.

 

[onmyway365]

Hi Rory,

Thanks for the response.

I have heard this mentioned, but it is a bit hazy. I was told I could make use of a helper sheet for this purpose.

Can you perhaps point me in the right direction?

Thank you very much!

 

[RoryA]

You seem to have plenty of people assisting you on other forums. It would have been nice if you had had the courtesy to provide links to let us know that - see rule 10 here, and especially the link within that rule.

 

[onmyway365]

Hi Rory,

Thanks. i did mention in my initial post re the cross posting, but can definitely do that again.

Herewith my links to forums assisting in this regard:

[SOLVED] Add a row with more than 10 columns to a ListBox?

Add a row with more than 10 columns to a ListBox? - Page 2

Thank you for everyone's help!