KB125805 - Displaying Several Dialog Boxes Without Looping

Article ID: 125805

Article Last Modified on 10/11/2006

Microsoft Excel 97 Standard Edition
Microsoft Excel 95 Standard Edition
Microsoft Excel 5.0 Standard Edition

This article was previously published under Q125805

SUMMARY

In Microsoft Excel, when you create a macro that displays several dialog boxes, you may want to display and hide the dialog boxes with some method other than a Do While...Loop. For example, a Do While...Loop may not be the most effective method if you want to cycle through several dialog boxes at a time. The loop is not the best method in this case, because, when you use this method, a dialog box is still being displayed and remains on the stack, even after the dialog box has been dismissed.

Instead of using a Do While ...Loop, you can use the OnTime method to run the macro at a specified time. Note, however, that when you use this method, there may be a delay before each dialog box is displayed (this delay does not occur with the looping method). To create an example that uses the OnTime method to display dialog boxes, follow the procedure in the "More Information" section of this article.

For additional information about solving this problem with the looping method, please see the following article(s) in the Microsoft Knowledge Base:

111867 XL5 Err Msg: "Not Enough Stack Space to Run Macro"

MORE INFORMATION

Visual Basic Code Example

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

Use the following steps to create a Visual Basic procedure that uses command buttons to cycle through four dialog boxes:

On a new Visual Basic module, type following code:

   Sub Dialog1Show()
      DialogSheets("Dialog1").Show 'Shows Dialog1.
   End Sub

   Sub Dialog1Hide()
      DialogSheets("Dialog1").Hide  'Hides Dialog1.
      Application.OnTime Now, "Dialog2Show"  'Runs Sub Dialog2Show.
   End Sub

   Sub Dialog2Show()
      DialogSheets("Dialog2").Show  'Shows Dialog2.
   End Sub

   Sub Dialog2Hide()
      DialogSheets("Dialog2").Hide  'Hides Dialog2.
      Application.OnTime Now, "Dialog3Show"  'Runs Sub Dialog3Show.
   End Sub

   Sub Dialog2HideBack()
      DialogSheets("Dialog2").Hide  'Hides Dialog2.
      Application.OnTime Now, "Dialog1Show"  'Runs Sub Dialog1Show.
   End Sub

   Sub Dialog3Show()
      DialogSheets("Dialog3").Show  'Shows Dialog3.
   End Sub

   Sub Dialog3Hide()
      DialogSheets("Dialog3").Hide  'Hides Dialog3.
      Application.OnTime Now, "Dialog4Show"  'Runs Sub Dialog4Show.
   End Sub

   Sub Dialog3HideBack()
      DialogSheets("Dialog3").Hide 'Hides Dialog3
      Application.OnTime Now, "Dialog2Show" 'Runs Sub Dialog2Show.
   End Sub

   Sub Dialog4Show()
      DialogSheets("Dialog4").Show  'Shows Dialog4.
   End Sub

   Sub Dialog4Hide()
      DialogSheets("Dialog4").Hide 'Hides Dialog4.
   End Sub

   Sub Dialog4HideBack()
      DialogSheets("Dialog4").Hide  'Hides Dialog4.
      Application.OnTime Now, "Dialog3Show" 'Runs Sub Dialog3Show.
   End Sub

Create four dialog sheets that each contain three buttons: OK, Cancel, and a third button with the text Go Back.
Follow the appropriate procedure below depending on whether you want to use the OK and Go Back buttons or the OK and Cancel buttons.
- To use the OK and Go Back buttons, assign the macros in the following manner:
```
         Dialog1     OK Button         Dialog1Hide

         Dialog2     OK Button         Dialog2Hide
                     Go Back Button    Dialog2HideBack

         Dialog3     OK Button         Dialog3Hide
                     Go Back Button    Dialog3HideBack

         Dialog4     OK Button         Dialog4Hide
                     Go Back Button    Dialog4HideBack
```
  When you run the first dialog box, the OK button should dismiss the current dialog box and show the next dialog box. The Go Back button should dismiss the current dialog box and show the previous dialog box.
- To use the OK and Cancel buttons, assign the macros in the following manner:
```
      Dialog1     OK Button      Dialog1Hide

      Dialog2     OK Button      Dialog2Hide
                  Cancel Button  Dialog2HideBack

      Dialog3     OK Button      Dialog3Hide
                  Cancel Button  Dialog3HideBack

      Dialog4     OK Button      Dialog4Hide
                  Cancel Button  Dialog4HideBack
```
  When you run the first Dialog box, the OK button should dismiss the current dialog box and show the next dialog box. The Cancel button should dismiss the current dialog and show the previous dialog box.

Additional query words: 5.00c XL

Keywords: kbprogramming KB125805