XL5: Custom Multiple Select List Box Returns Incorrect Result

The information in this article applies to:

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0

SYMPTOMS

In Microsoft Excel, if you run a macro that displays a custom dialog box with a multiple selection list box that was created in Microsoft Excel version 3.0 or 4.0, when you select an item or items in the list box and choose OK, the result returned for the item selected in the list box is incorrect.

CAUSE

When you select an item from a multiple selection list box in a custom dialog box, a name is created on the sheet that contains the dialog box definition table. This name stores the number or numbers that correspond to the items that you select in the list box. The values are stored as an array, and the name created to store these values is the name that you define in the custom dialog box definition table. To specify the name of this result, type the name in the Init/Result column of the list box line (item number 15) in the dialog box definition table.

When the dialog box definition table is not contained on the macro sheet with the macro that runs it, and you open the files that contain the dialog definition and the macro that runs the dialog box in Microsoft Excel version 5.x or 7.0, when you run the dialog box, the name created to store the values you select is created as a local name, but the values returned by the macro are those contained in the global name. The values returned are the values that you selected in the list box the last time you ran the dialog box.

WORKAROUND

To avoid having incorrect results returned when you run a macro that displays a custom dialog box with a multiple selection list box that was created in Microsoft Excel versions 3.0 or 4.0, do either of the following:

• Create the dialog definition on the macro sheet that contains the macro that runs the custom dialog box.

  -or-

• In the macro that runs the dialog box, wherever you refer to the name that stores the selected items in the list box, use the sheet name in the reference to refer to the local name. For example, if you use the following line in the macro that runs the dialog box to display the number of the selected item

        =ALERT(INDEX(DIALOG.XLS!selection,1,i))
  

  where DIALOG.XLS is the name of the file that contains the dialog definition, and "selection" is the name of the result, change the line to the following

        =ALERT(INDEX([DIALOG.XLS]DIALOG!selection,1,i))
  

  where DIALOG is the name of the sheet in the DIALOG.XLS workbook that contains the dialog definition.