XL: Problems Using Show Method to Open Dialog Sheets

ID: Q111308

The information in this article applies to:

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

SUMMARY

In Microsoft Excel, when you use the Show method to display a dialog box, the dialog box must be closed or hidden before you can return to the Visual Basic procedure that called it.

MORE INFORMATION

The Show method runs a specified dialog box, either by running event procedures immediately, or by running the dialog box and waiting for the user to enter data.

If the dialog box is called during a procedure in which another dialog box is already present, the Show method will run the dialog box on top of the present dialog box. To return to the calling procedure, the second dialog box must be closed or hidden.

Closing the Dialog Box

If the dialog box has a button with the DismissButton property set to TRUE:

When you Click a button with the DismissButton property set to TRUE, you will automatically close a user-defined dialog box. This is the preferred approach, because the DismissButton property will validate all edit fields before closing the dialog box and the Show method will return TRUE if validation succeeds. If validation does not succeed, an alert is displayed and the invalid edit field is activated.

If your dialog box does not contain a DismissButton that is set to TRUE, the dialog box can be closed by doing one of the following:

Click the Cancel button to close the dialog box without executing any actions specified by the dialog box controls.
```
    -or-
```
Click the Exit box or press ESC to close the dialog box without executing any actions specified by the dialog box controls.
NOTE: If a button in the dialog box has its CancelButton property set to TRUE, clicking the Exit box or pressing ESC will cause that button to be selected. Any macro specified by the button's OnAction property will run before the dialog box is closed.
```
    -or-
```
Use the Hide method to mark the dialog box for closing (the dialog box will not actually be closed until the procedure that used the Hide method is completed).
NOTE: If you call the Hide method to mark the dialog box for closing, if you then follow that action with another Show method, then the new dialog box will be run on top of the hidden first dialog box. This often creates problems with procedures in the second dialog box due to a lack of stack space. To avoid this lack of stack space, make sure that each new dialog box is completely closed, not just hidden, before running the next dialog box. The focus will return to the original calling procedure when the dialog boxes are dismissed.

REFERENCES

For more information about the Show Method, click the Search button in Visual Basic help and type:

   Show Method

For more information about the Hide Method, click the Search button in Visual Basic help and type:

   Hide Method

For more information about the DismissButton Procedure, click the Search button in Visual Basic help and type:

   DismissButton Procedure

For more information about the CancelButton Procedure, click the Search button in Visual Basic help and type:

   CancelButton Procedure

Additional query words: 5.00 7.00 8.00 97 VB memory VBE

Keywords          : kbdta PgmOthr PgmHowto KbVBA 
Version           : WINDOWS: 5.0,5.0c,7.0,7.0a,97; MACINTOSH: 5.0,5.0a
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: December 9, 1998