Solved: Splash screen While Macro Runs

[BENSON]

I know how to create a start up splash screen ,I now want to show a splash screen while a macro is running .The macro takes 39 secs to run, so I would want the splash screen to be display for this amount of time on comencement of the macro.I would appreicate any pointers you guys can give me.

Thanks

[Charlize]

youruserform.Show (0)
Charlize

[Bob Phillips]

How about a progress bar?

http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.htm

[lucas]

In thisworkbook module:
Private Sub Workbook_Open()
UserForm1.Show
End Sub


in the module for your userform(used as a splashscreen):
Private Sub UserForm_Initialize()
Application.OnTime Now + TimeValue("00:00:39"), "KillForm"
End Sub

in a standard module:
Sub KillForm()
Unload UserForm1
End Sub

[CBrine]

Benson,
I think Charlize's solution is the best one for you, you just need a few more details.

1. Build a splash screen user form.
2. add the code
Userform1.show (0)'Or whatever your user screen name is.
To the beginning of your macro.
3. Add the code
unload Userform1
To the end of your macro.

Lucas,
Your's would work, but I find it hard to believe the macro runs in exactly 39 seconds every time it is run!! Depending on the load on your system this is going to be variable. Also what happens if it is run on a faster or slower PC?

HTH
Cal

[lucas]

Hi Cal
My computer keeps pretty good time no matter what the load as they all should...??? But that's not the point. If they want something exact they should follow Bob's suggestion and use a progress bar that incriments as it goes. Hard to set up if there are several procedures.

I basically tried to give the OP what he asked for. Not always the best answer.

[BENSON]

Thanks for all your help guys ,I have got the splash screen to show while the macro is running and dissappearing when the macro has finished.One problem is that the picture on the splash screen does not show.Do I have to change the "enabled to false "in the user forms properties box ?

Thanks again

[Bob Phillips]

Chuck in a DoEvents in the form startup, before the macro starts.

[CBrine]

My computer keeps pretty good time no matter what the load as they all should...???
No worries, like I said, yours would do what he's requesting, but if you read between the lines??

My thoughts were just that if he's showing a splash screen, it's mostly likely not for his own benefit, unless he likes pretty pictures. So the splash screen is for the user's benefit. Since it's not going to be running on his own PC, the 39 seconds may not be accurate.

I think it was the Col Mustard in the dining room.:-)

Cal

[BENSON]

MANY THANKS GUYS FOR ALL YOU HELP MY SPLASH SCREEN WORKS GREAT