New Excel Forum

This forum has been moved to

Ask all future questions in the New Excel Forum.


Wait Message While Macro Is Working

Free Excel Macros

Wait Message While Macro Is Working

Postby Sisyphus » Fri Dec 28, 2012 12:05 am

XlWait is a little UserForm which you can show to the user of your macro asking him to wait while your macro is doing its job. The attached workbook is entirely made for the purpose of demonstration. It contains an event procedure in Sheet1's code sheet that responds to the button on the worksheet and calls the procedure WaitMan.Test_Wait. This procedure in turn calls the procedures
  1. WipeSheet
  2. WriteNumbers
  3. WriteResult and, finally,
  4. XlWait.EndXlWait
XlWait is the UserForm already above mention. Essentially it contains these two procedures:
  1. ShowXlWait and
  2. EndXlWait (already mentioned above)
Each of the procedures WriteNumbers and WriteResults has a call of the sub CallXlWait which in turn is calling XlWait.ShowXlWait. Inserting CallXlWait as a buffer makes for simpler syntax. XlWait.ShowXlWait needs 3 parameters.
  1. A message
    for example, "Calculating results"
  2. A definition of the entire task
    for example, 64000 rows
  3. And a marker of how far the program has progressed to-date
    for example, row No. 32000
From this information XlWait.ShowXlWait will produce a message like "Calculating results ... 50% done" and show a bar of a length equal to the percentage. In the attached workbook XlWait is being called after each row. In order to save time it executes only each 8th time. This is a parameter that can be set in CallXlWait. When the code ends XlWait.EndXlWait removes the UserForm.

The good news is that you can drag the form XlWait, along with all its code, into your own project. It needs no modification. You can use the sub CallXlWait or its code to call XlWait.ShowXlWait. However, you will have to determine the criteria of progress depending upon the realities in your own project and feed numbers to the form that its code can convert into a report.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Former Moderator
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 10 guests