XL2000: Dialog Box Show Method Fails with Reference Type Edit Box (213822)

SYMPTOMS

In Microsoft Excel 2000, 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 the Formula edit validation type, you may receive the following error message:

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

This error message 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 behavior is a known limitation with Microsoft Excel 2000-style dialog boxes. You cannot stack a semi-modeless dialog box (any dialog box that has a Reference or Formula edit box type) 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

Select the edit box.
On the Format menu, click Control.
Click the Control tab, and then select a type other than Reference or Formula.

Method 2: Unload the First Dialog Box Before Showing the Second

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

Method 3: Use UserForms

If you are using Microsoft Excel 2000, use UserForms instead of Microsoft Excel 5.0 dialog boxes. UserForms do not have this limitation.

For more information about using UserForms, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type userform object, userforms collection in the Office Assistant or the Answer Wizard, and then click Search to view the topic.