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
- WipeSheet
- WriteNumbers
- WriteResult and, finally,
- XlWait.EndXlWait
XlWait is the UserForm already above mention. Essentially it contains these two procedures:
- ShowXlWait and
- 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.
- A message
for example, "Calculating results" - A definition of the entire task
for example, 64000 rows - 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!
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Sisyphus
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)