macro running .... message box ? is it possible?

[ap1]

i am running a macro and i have included Application.ScreenUpdating = false. Is there a way a message can be displayed saying macro executing please wait.... when the macro is running in the background.

The only possibility I know is to use the status bar (grey bar on the bottom left of the screen, which usually says "Ready").

To change the text of the status bar, use the code

Application.StatusBar = "Any Text"

At the end of your macro, you should put the code

Application.StatusBar = False

otherwise the last message will remain after your macro has ended.

[ap1]

Thanks. That helps.

[CRWeaks23]

This may be a little too much than what your asking for, however you could use a user form to create a percentage indicator, just like you see in other windows applications. Create a label on the form and set the background color to something like blue or red. Then just stretch the label within the code after a certain number of statements have been executed. For this to work, simply put your existing macro in the UserForm_Initialize() event, and create a function that will set the bar to a specified length with parameters, and throughout your macro place call statements to this "stretching" procedure. Its a REALLY cool way to show a "working in the background" status bar. If you want more info just post.

Chris

[ap1]

Ya more info will help thanks

[CRWeaks23]

Ok, I looked all over to find a web page displaying this, with no success. So I will try to explain it the best way i can without writing the code, because it can get rather lengthy...Also note that I told you to place the code in the Initialize event...dont do this! I have a remedy below...

o Build a userform (not to large), and change its Caption property to "ProgBar"

o Add a Frame Control in the center, and name it "FrmProg"

o Add a label control inside of the Frame and make its background color blue, name this LblProg, and change its special effect to frmSpecialEffectRaised

o Now you should have a rectangular Userform, a frame in the center with a long bar inside the frame (the bar is just the label with a blue background)

o Place the following code in the code for the userform, replacing xxx with your prodedure (the one you want to have running in the background while the status bar is in front)
Private Sub UserForm_Activate()
Call xxx
End Sub

o Insert the following code after that:
Sub UpdateProg(Pct)
With ProgBar
.frmProg.Caption = Format(Pct, "0%")
.LblProg.Width = Pct * (.frmProg.Width - 5)
.Repaint
End With
End Sub

o YOUR ALMOST DONE! Now, you want to insert calls to UpdateProg throughout your xxx procedure, the with the parameter Pct equal to the percent your xxx procedure is completed. For example, when the code is 50% done, place "Call UpdateProg(.5)" in your xxx procedure.

o To get it to run, instead of calling the xxx procedure, load the form and show it, NEVER CALLING XXX (this is important). Do this by inserting the following wherever you initially called XXX, call it say ShowProgIndicator:
Sub ShowProgIndicator()
Load frmProg
frmProg.LabelProg.width=0
frmProg.Show
End Sub


WHEW taht was a lot... Remember the key is to situate all teh objects on your form so it looks like a progress bar youve seen before, then run the code...GOOD LUCK!!!

Chris

[MrExcel]

I could've sworn someone had this on the 'net, but now I think I remember seeing it in J-Walk's Power Programming with Excel 2000 book - yes pages 396-400.

It is a pretty cool effect - I've used it a few times.

Bill

[CRWeaks23]

I Knew it was from somewhere because i remember seeing it a while ago. I couldnt find it to post though (I even looked through J-walks site for the Power Programming in VBA book).

[CRWeaks23]

I'll also note Microsoft actually has a similar version posted on their excel site, but this one is much nicer