How to Create a Temporary Message Box 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.