Listbox Multi column error

[kippers2]

Hi,
I have a simple 13 column listbox
for some reason I cannot allocate value to column 10 onwards
I have created a simple version and it still does not work
Is there a maximum number of coulumns or is there a setting in the list box i need to set?

[VBA]
Sub test()
userform1.ListBox1.AddItem "1"
userform1.ListBox1.List(0, 1) = "2"
userform1.ListBox1.List(0, 2) = "3"
userform1.ListBox1.List(0, 3) = "4"
userform1.ListBox1.List(0, 4) = "5"
userform1.ListBox1.List(0, 5) = "6"
userform1.ListBox1.List(0, 6) = "7"
userform1.ListBox1.List(0, 7) = "8"
userform1.ListBox1.List(0, 8) = "9"
userform1.ListBox1.List(0, 9) = "10"
userform1.ListBox1.List(0, 10) = "11"
userform1.ListBox1.List(0, 11) = "12"
userform1.ListBox1.List(0, 12) = "13"
userform1.Show
End Sub
[/VBA]

[Bob Phillips]

As it says in help ... For an unbound data source, there is a 10-column limit (0 to 9).

[kippers2]

Thanks- I missed that
Therefore the only work around is to work with range and then assign using rowsource
Unless you have any other suggestions?

[Bob Phillips]

Seems so, although I have never used a listbox with more than 10 columns personally.

[tpoynton]

I'm curious...why do you need more than 10 columns? can any of the columns be combined into 1 column usefully (e.g., last name and first name)?

like:
[vba]userform1.ListBox1.AddItem "1" & ", " & "2"[/vba]

[kippers2]

The list box gives the user stocks of materials located around the country, wieghts, shipping costs, Moving average price etc and pre selects some of the lines as a recommendation of the cheapest options to get the stock
I let the operator see all stock options so they can change there selection
The idea of concaternating columns is a very good one as some of the columns are info only - wish I had thought of that !! Will test

[Bob Phillips]

As an alternative, why not have two listboxes, both single ciolumn. The first displays all items but a recognisable key, the second displays all other columns of the SELECTED item, but as rows of the listbox.

[Andy Pope]

Here is a post I made in the MS newsgroups on the subject of listbox column limits.

Using additem the limit is 10
Using RowSource 256
Using variant array unlimited

On a blank worksheet put this formula in A1

A1: =ADDRESS(ROW(),COLUMN())

Fill the formula across range A1:IV10

Now create a userform with 3 listboxes and 1 commandbutton.
Paste the following code.

[vba]
Private Sub CommandButton1_Click()

Dim lngCol As Long
Dim lngRow As Long
Dim vntData As Variant

With Range("A1:IV10")
' 256 columns
ListBox1.ColumnCount = .Columns.Count
ListBox1.RowSource = .Address

' 256 columns form variant array
vntData = .Value
ListBox2.ColumnCount = .Columns.Count
ListBox2.List = vntData

' additem approach
On Error GoTo ErrAddColumn
For lngCol = 1 To .Columns.Count
ListBox3.ColumnCount = lngCol
For lngRow = 1 To .Rows.Count
If lngCol = 1 Then
ListBox3.AddItem .Cells(lngRow, lngCol)
Else
ListBox3.List(lngRow - 1, lngCol - 1) = _
Cells(lngRow, lngCol)
End If
Next
Next
End With
ErrAddColumn:
Exit Sub

End Sub
[/vba]

But just because you can use 256+ columns does not mean you should