XL 2003 VBA form Listbox-limitation in max number of columns
 

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

XL 2003 VBA form Listbox-limitation in max number of columns
 

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

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

XL 2003 VBA form Listbox-limitation in max number of columns
 

I don't understand how your question about listboxs and the maximum number of
columns fits in with 15 dropdowns in the same userform--but I guess it really
doesn't matter.

I'd rather use columns than concatenation as a general rule--but if it looks
nice, why not?

Sajeeth wrote:

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


--

Dave Peterson