XL5: Running Custom Dialog Box Returns #N/A in Result Column

• Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel, when you run a custom dialog box and you select a list box item, the #N/A error value, rather than the number of the item selected in the list box, is returned to the result column.

CAUSE

This behavior occurs when you run the dialog box from the sheet that contains the referenced list box items, instead of from the macro sheet that contains the dialog box definition table.

For example, if the macro sheet TEST contains a dialog box definition table and the Dialog.Box function, and you run the dialog box from a worksheet that contains a list of items referenced in the dialog box, the #N/A error value is returned in the result column in the dialog definition table for the list box instead of the value of the item selected in the dialog box.

This behavior occurs because the dialog box tries to return values to the worksheet that contains the list, instead of returning them the macro sheet that contains the dialog box definition table.

WORKAROUNDS

To avoid returning the #N/A error value in the result column in the dialog definition table, do any of the following:

• Include any items that will be referenced in a list box on the macro sheet that contains the dialog box definition table.

  -or-

• Run the macro that displays the dialog box while the macro sheet that contains the dialog box definition table is active.

  -or-

• Use a dialog sheet to create a custom dialog box, and return values when you run the dialog box using a Visual Basic procedure.

   ARTICLE-ID: Q111564
   TITLE     : XL5: Determining which Items Are Selected in a List Box

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0c for Windows.