Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I save an excel file with long number columns in cvs form. | Excel Discussion (Misc queries) | |||
Incrementing a receipt number in Excel 2003 VB form | Excel Programming | |||
Form limitation | Excel Programming | |||
Transfer multiple columns items form listbox to range | Excel Programming | |||
ListBox Rowsource Limitation?? | Excel Programming |