XL: Dialog Box Show Method Fails with Reference Type Edit Box

ID: Q131237

The information in this article applies to:

Microsoft Excel for Windows, version 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for the Macintosh, version 5.0, 5.0a
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows

SYMPTOMS

In the versions of Microsoft Excel listed at the beginning of this article, when you attempt to display a dialog box over another dialog box, and the first dialog box contains a text box that has the Reference or Formula "Edit Validation" type, you may receive the following error message:

   Run-time Error '1004':
   Show method of Dialogsheet class failed

This error occurs when the second dialog box is displayed during an event macro of a control on the first dialog box, and the first dialog box has not been unloaded.

CAUSE

This is a known limitation with Microsoft Excel 5.0 style dialog boxes. You cannot stack a semi-modeless dialog box (any dialog box that has a Reference or Formula type edit box) on top of another dialog box.

WORKAROUND

To work around this limitation, use any of the following methods.

Method 1: Change the Edit Box Type

1. Select the edit box.

2. On the Format menu, click Object.

3. Click the Control tab, and then click a Type other than Reference or

   Formula.

Method 2: Unload the First Dialog Box Before You Show the Second

Unload the first dialog box before you show the second dialog box (which contains the Reference or Formula type edit box).

Method 3: Use UserForms

If you are using Microsoft Excel 97, use UserForms instead of Microsoft Excel 5.0 dialogs. UserForms do not have this limitation.

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q111867
   TITLE     : XL Err Msg: "Not Enough Stack Space to Run Macro"

Microsoft Excel 97

For information about using UserForms in Microsoft Excel 97:

1. Start a new workbook and press ALT+F11 to activate the Visual Basic

   Editor.

2. On the Help menu, click Contents and Index.

3. Click the Index tab, type "UserForms Collection" (without the

   quotation marks), and then click Display.

Additional query words: validate XL97 XL7 XL5

Keywords          : kberrmsg kbprg kbdta kbdtacode PgmCtrlsStd KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug
Solution Type     : kbnofix

Last Reviewed: December 9, 1998