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

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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.
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.