Splashscreen whilst iterating & calling a sub on break in code


Posted by Dan on August 09, 2000 4:15 AM

I wish to do two things:

Firstly, I have a spreadsheet setup which requires ~2000 iterations. My code works fine
for this but as this takes some time I wish to have a splashscreen (possibly a form with a label)
notifying the user that "calculation in progress...". Either this or change the status bar to read
"calculating..." during the iteration process.

Secondly, There is a sub that is called when the iteration process is completed. If the user breaks
the iteration loop, the code is exited without the sub being called. Is there some way round this so that
the sub is called even if this happens.

Cheers!!!!!

Dan



Posted by Mike on August 09, 0100 7:45 AM

For the splashscreen, try formatting a worksheet with a message you
want to display. While running the macro, you can run this code at the
beginning:

wsheet=activesheet.name
sheets("SplashScreen").select
application.screenupdating=false
sheets(wsheet).select

The user will see the splashscreen while
running the macro, until it either completes,
or they break out of the macro.

If you wish to change the statusbar, try:

Application.Statusbar = "Please wait. Performing calculations..."

and have
Application.Statusbar = False
at the end of your macro.
Only snag with the statusbar is that if the macro
is broken out of, the Statusbar stays locked with
that text.

As for that sub that is called at the end, you don't have
many options. You could disable user input so that they
can't break out, and have to let the macro finish. =^)
What you really want is some OnMacroEnd event handler which
could kick off another sub. But something like that may
be used to create bad viruses.