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

• Microsoft Excel for Windows, versions 3.0 and 4.0
• Microsoft Excel for the Macintosh, versions 3.0 and 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

REFERENCES

"Microsoft Excel User's Guide," version 3.0, pages 632-634

"Microsoft Excel User's Guide 2," version 4.0, pages 276-279