Listbox for more than 256 items (Excel 97)

[StevePB]

I need to be able to display a listbox on a form, which will accept up to 999 items in the list (populated by an array or from a range on a spreadsheet).

Does anyone have a suggestion for the best way to do this, as I have found that the listbox will not accept more than 256 items ? (I would like to maintain scrollability if possible).

 

[SkipVought]

Hi,
Try referencing the .RowSource property using the ComboBox. Name the list on your spreadsheet. For instance, if you name the list range on your spreadsheet, "CoomboList", then type ComboList in the Properties for the ComboBox in the .RowSource. Skip,
metzgsk@voughtaircraft.com

 

[StevePB]

Thanks for your suggestion Skip, but I have tried using the named range as the row source for the listbox, but it will not handle more than 256 list items.

I'm thinking maybe I have to have an array that holds 256 of the items from the list, and somehow regenerate the array with a new range of numbers when the user tries to scroll down past the 256th item - but this sounds horrible!

 

[nath]

I'm getting quite confused. I use listBoxes and seeing your question I thought I'd check them in case they grow more then 256 items. And It doesn't seem to be a problem. I work with Excel 97, have my Listbox (or combobox) on a UserForm and populates the item either with the RowSource property or item by item. Either case it takes more than 256 without trouble! How can I recreate the problem?

Nath

 

[StevePB]

Hi Nath,

The problem arises under the following conditions:

I have to put the contents of a list in a spreadsheet into a listbox. The list has three columns, and is a different length each time I access it, so I name the range that contains the list, and use the named range as the row source for the listbox.

This works ok until I try to load a list with more than 256 items, then the macro just hangs.

If you want to see the code I can send it to you.

Regards, SB
steve.brodrick@daler-rowney.com

 

[SkipVought]

How can you use a multi-column list as your row source? I do not understand. Skip,
metzgsk@voughtaircraft.com

 

[nath]

I would be interested in the code if you don't mind. I tried with naming a range and again it worked fine.

Nath

 

[StevePB]

Hi Skip,

A simplified version of the code is as follows:

[tt]
Sub testRange()

Range("a1", "c3&quot.Select 'a list with 3 rows and 3 columns

ActiveSheet.Names.Add Name:="TestList", _
RefersTo:="=" & Selection.Address

lstMyListBox.ColumnCount = 3
lstMyListBox.RowSource = "TestList"

End Sub
[/tt]

In my case the range is specified by dynamically selecting all the cells that compose the list.

Regards, SB.

 

[SkipVought]

Hi,
I used

lstMyListBox.ListFillRange = "TestList"
[/code}
and got 999 rows. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

and...

for better programatic manipulation try using the Cells(RowNbr, ColNbr) notation rather than "A1" notation like this....

Range(Cells(1, 1), Cells(3, 3)).Select    'select a range of 3 rows & 3 columns

MUCH easier to manipulate! Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Steve,
I don't think that there is a row limit or byte limit on the Listbox. I exported your form and pared down the initialization code to just define the list to the box and got 999 rows, 3 columns with 45 bytes per row. But, maybe, with everything else that's in memory, some other kind of limit is being hit -- I DON'T KNOW.
Maybe there's an Gates Guy out there that could shed some light on this one. Skip,
metzgsk@voughtaircraft.com