OFF98: How to Display "Now Processing" While a Macro Runs

ID: Q184256

The information in this article applies to:

SUMMARY

This article contains information about displaying a dialog box while a macro is running in the background. The purpose of displaying a dialog box is to alert the user that the application is busy while the macro is running.

MORE INFORMATION

When you use the Show method to display a UserForm, the UserForm is "modal"; that is, the user must respond to the UserForm before using any other part of the application. After a UserForm is loaded, no subsequent code is executed until the UserForm is either hidden or unloaded.

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/
To display a dialog box and allow the macro to run in the background, you can run the macro in the Activate event of the UserForm. When the UserForm is displayed, the Activate event is triggered, and the macro is run while the UserForm is displayed on the screen. To do this, follow these steps: 

 1. Create a new workbook in Microsoft Excel 98 (or a new document in
    Microsoft Word 98).

 2. Press OPTION+F11 to start the Visual Basic Editor.

 3. On the Insert menu, click Module to insert a module into the project.

 4. Press F6 to display the Properties window.

 5. Change the Name property of the module to "Processing_Code" (without
    the quotation marks).

 6. In the Processing_Code module window, type the following:

      Public Processing_Message As String
      Public Macro_to_Process As String

      Sub StartProcessing (msg As String, code As String)

         Processing_Message = msg    ' Set the message that is displayed
                                     ' in the dialog box.

         Macro_to_Process = code     ' Set the macro that is run after the
                                     ' dialog box is active.

         Processing_Dialog.Show      'Display the dialog box.

      End Sub

 7. On the Insert menu, click UserForm to add a UserForm to the project.

 8. Press F6 to display the Properties Window. Change the UserForm
    properties to the following settings.

       Property           Setting
       -------------------------------------

       Name               Processing_Dialog
       Caption            (Leave Blank)
       StartUpPosition    2-CenterScreen

 9. Add one Label control to the UserForm. Change the Name property of the
    label to "lblMessage."
10. Select the UserForm. On the View menu, click Code. In the Code window, 
    in the Procedure list, click the Initialize event, and then type the
    following in the Code window:

       Private Sub UserForm_Initialize()

          lblMessage.Caption = Processing_Message  ' Change the Label
                                                   ' Caption.

       End Sub
11. In the Code window, click Activate event in the Procedure list, 
    and then type the following:

       Private Sub UserForm_Activate()

          Me.Repaint                         ' Refresh the UserForm.
          Application.Run Macro_to_Process   ' Run the macro.
          Unload Me                          ' Unload the UserForm.

       End Sub
12. On the Insert menu, click Module. Type the following code in the 
    Code window:

       Sub MyMacro()

          For x = 1 to 5000
             Application.StatusBar = x   ' 5000 Iterations Changing
                                         ' StatusBar
          Next

          Application.StatusBar = False  'Reset the StatusBar

       End Sub

       Sub Main()

          ' Call the StartProcessing procedure to display the
          ' Processing_Dialog with the label "Processing, Please Wait..."
          ' and execute MyMacro.

          StartProcessing "Processing, Please Wait...", "MyMacro"

       End Sub
13. Press COMMAND+Q to close the Visual Basic Editor and return to 
    Microsoft Excel (or Microsoft Word).
14. On the Tools menu, point to Macro, and click Macros. Click the Main 
    macro and click Run. The Processing_Dialog dialog box appears.
While the dialog box is displayed, the status bar text in the application increments from 1 to 5000. The changing status bar text is the indication that the macro is running while the dialog box is displayed on the screen.

Additional query words: OffInterop xlvbahowto xlvbainfo 8.00 execute executing background xl98 wd98 splash screen OFF98 

Keywords          : kbcode kbdta 
Version           : MACINTOSH:98
Platform          : MACINTOSH
Issue type        : kbhowto

Last Reviewed: April 6, 1999