View Full Version : Listbox Multi column error
kippers2
09-17-2007, 10:36 PM
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?
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
Bob Phillips
09-18-2007, 12:05 AM
As it says in help ... For an unbound data source, there is a 10-column limit (0 to 9).
kippers2
09-18-2007, 02:44 PM
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
09-18-2007, 03:22 PM
Seems so, although I have never used a listbox with more than 10 columns personally.
tpoynton
09-18-2007, 03:53 PM
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:
userform1.ListBox1.AddItem "1" & ", " & "2"
kippers2
09-19-2007, 02:56 PM
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
09-19-2007, 03:23 PM
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
09-20-2007, 04:04 AM
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.
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
But just because you can use 256+ columns does not mean you should ;)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.