XL 2003 VBA form Listbox-limitation in max number of columns
Hi Dave,
Thanks for your answer.
The code given by you adds the value in the list box where as in my program
the data sources are 15 dropdowns in the same form . I understand this is an
option. Also, want to check if a concat to accomodate more values in a single
column a good practice?
Thanks,
Sajeeth
"Dave Peterson" wrote:
From xl2003 VBA's help for .columncount:
For an unbound data source, there is a 10-column limit (0 to 9).
So use .additem instead or pickup the values all at once:
Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long
With Worksheets("sheet9999")
Set myRng = .Range("a1:A" _
& .Cells(.Rows.Count, "A").End(xlUp).Row).Resize(, 5)
End With
With Me.ListBox1
.ColumnCount = myRng.Columns.Count
For Each myCell In myRng.Cells
.AddItem myCell.Value
For iCtr = 2 To myRng.Columns.Count
.List(.ListCount - 1, iCtr - 1) _
= myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
End Sub
or
Option Explicit
Private Sub UserForm_Initialize()
Dim myRng As Range
With Worksheets("sheet9999")
Set myRng = .Range("a1:A" & _
.Cells(.Rows.Count, "A").End(xlUp).Row).Resize(, 5)
End With
With Me.ListBox1
.ColumnCount = myRng.Columns.Count
.List = myRng.Value
End With
End Sub
Sajeeth wrote:
Hi,
What is the maximum number of columns i can have in a XL 2003 form Listbox
control. It is not accepting anything more than 10.
I have a requirement where the item has 15 columns to be displayed. Any work
around for this?
Thanks,
Sajeeth
--
Dave Peterson
|