XL: How to Create a Temporary Message Box While Macro Runs

ID: Q148209


The information in this article applies to:
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a


SUMMARY

In Microsoft Excel, you cannot display a message box at the same time a second macro is running. However, you can simulate a message box with a text box when using a Visual Basic for Applications macro. To do this, you would create a text box and position it on the screen at an appropriate location. With code, you can present the text box, run your second subroutine, and, at the conclusion of the subroutine, delete the text box. The example in this article demonstrates this process. You can change the design of the text box to look anyway you want. The following example gives you just one way to display it.


MORE INFORMATION

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/overview/overview.asp
For information about showing a message box in Excel 97 for Windows and Excel 98 Macintosh Edition, please see the following article in the Microsoft Knowledge Base:
Q162257 OFF97: How to Show a "Now Processing" Dialog While Macro Runs

Visual Basic Code Example

  1. Type your name in cell A1 of Sheet1 in a new workbook.


  2. On the Insert menu, click Macro, and then Module.


  3. On the new module sheet, type the following two subroutines:
    
          Sub DisplayTextMsgBox()
             ' Select the first worksheet.
             Worksheets(1).Select
    
             ' Create a text box on the active worksheet.
             ActiveSheet.TextBoxes.Add(215, 195, 91.5, 60).Select
    
             ' Store the name of Worksheet in variable StoreWSNM.
             StoreWSNM = ActiveSheet.Name
    
             ' Store the name of Text Box in variable StoreNM
             StoreNM = Selection.Name
    
             ' Set the Font and Border properties of the text box.
             With Selection
                With Selection.Characters.Font
                   .Name = "Arial"
                   .FontStyle = "Bold"
                   .Size = 20
                End With
                With Selection.Border
                   .LineStyle = xlContinuous
                   .ColorIndex = 1
                   .Weight = xlThick
                End With
    
                'Set round corners for the text box.
                .RoundedCorners = True
    
                'Set message text color to black.
                .Interior.ColorIndex = 15
    
                'Assign message text to the text box.
                .Characters.Text = "Please Wait..."
             End With
    
             ' Actual macro that will run while Please Wait...
             ' message is being displayed.
             Second_Macro
    
             ' Makes sure the proper Worksheet is selected.
             Worksheets(StoreWSNM).Select
    
             ' Makes sure the proper text box is selected.
             ActiveSheet.TextBoxes(StoreNM).Select
    
             ' Deletes the Please Wait... text box.
             Selection.Delete
          End Sub
    
          ' Note that the Please Wait... text box will be displayed
          ' until this macro has completed.
    
          Sub Second_Macro()
    
             ' Select A1 and copies it.
             Range("a1").Select
             ActiveCell.Copy
    
             ' Set loop to occur 5 times.
             For LoopIt = 1 To 5
    
                ' Move down one row and paste the contents of A1.
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
    
                ' Waits one second before looping.
                ' NOTE: This is only done for demonstration purposes to
                ' slow down the macro so the Please Wait text box will
                ' be displayed for at least 5 seconds.
                Application.Wait Now + TimeValue("00:00:01")
             Next
          End Sub 


  4. On the Tools menu, click Macro. In the Macro box, select the macro called DisplayTextMsgBox, and click the Run button.


NOTE: If your second macro will be selecting other worksheets while running, you may want to turn off screen updating at the beginning of the second macro. This will make sure the Please Wait text box remains on screen while that macro runs. You can use Application.ScreenUpdating = False to turn off screen updating.

Additional query words: 5.00a 5.00c

Keywords : kbcode kbprg PgmHowto
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.