Microsoft KB Archive/125805

= Displaying Several Dialog Boxes Without Looping =

Article ID: 125805

Article Last Modified on 10/11/2006

-

APPLIES TO


 * 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"



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: <pre class="fixed_text">     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. </li></ul> </li></ol>

Additional query words: 5.00c XL

Keywords: kbprogramming KB125805

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.