Microsoft KB Archive/90790

{|
 * width="100%"|

Excel: List Boxes in Custom Dialogs Limited to 3,990 Items

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0

-

SUMMARY
In versions 3.0 and 4.0 of Microsoft Excel, lists displayed in list boxes within custom dialog boxes are limited to 3,990 items. If the list associated with your list box contains more than 3,990 items, only the first 3,990 items will be available when your dialog box is displayed.

MORE INFORMATION
The text column (6th column) for a list box item contains a reference to the list. This reference can be an R1C1-style cell reference, a defined name, or a formula that returns an array. In all cases, the equal sign is omitted.

For example, the following could be entered in the text column for a list box:

OFFSET(MonthList,1,0,ListSize,1)

Where MonthList is the name defined for a cell just above your list on the same worksheet as the dialog definition table, and ListSize is a variable containing the number of items in the list. Note that there is no equal sign (=) before OFFSET.

In this case, if ListSize is greater than 3,990, only the first 3,990 items will show in the list.

The following table shows the item numbers for the various list boxes:

The item number is found in the first column of a custom dialog definition table.

Item# Type
15    List Box 16    Linked list box 21    Drop-down list box 22    Drop-down combination edit/list box