Excel Display a "Please Wait" Message

Have you ever had one of those macros that seem to take forever to complete? If this is a problem with your macro, you can have Excel display a "Please Wait" message to the user.

Most users expect code to run and complete almost instantaneously. This doesn't always happen. Recorded macros in particular generally take much longer to complete than well-written VBA code. To add to the problem, Excel VBA code is generally slower than a lot of other types of code.

Fortunately, you can use a bit of extra code to create a "Please Wait" message so that users knowthe code is running and Excel has not locked up on them! Unfortunately, one of the more popular ways to let users know code is running is via the use of an Excel progress meter.

There are two problems with this method. First, the progress meter can slow down your code even more, compounding the issue. Second, your slow code must be caused by a loop, and you cannot use the macro recorder to create a loop.

We prefer using VBA code, such as the following DoIt macro, which uses a rectangle found in Insert → Illustration → Shapes:

Sub DoIt( )Application.ScreenUpdating = TrueWith Sheet1.Shapes("Rectangle 1").Visible = msoTrue = (Not .Visible)End With'Forces TextBox to show while code is runningSheet2.SelectSheet1.SelectEnd Sub

To use this code, add a rectangle from the Drawing toolbar to any sheet in the appropriate workbook. While the rectangle is selected, click in the Name box and name the rectangle Rectangle1 (if it's not already called that).

Enter the text you want displayed while your code is running, and format, position, and size the rectangle as desired. Enter the preceding DoIt macro into a standard module of your workbook. If necessary, change Sheet1 in the code to the CodeName of the sheet on which you placed Rectangle1. Then select Developer → Code → Macros or Alt/Option-F8 (pre-2007, Tools → Macro → Macros) and run DoIt from within Excel. This will hide Rectangle1 completely.

At the very start of the slow code, place the following:

Run "DoIt"Application.ScreenUpdating = False

The use of Application.ScreenUpdating = False stops screen flicker and speeds up macros. At the very end of the slowcode, simply place the code Run "DoIt". Then run your macro as usual.