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

ap1

New Member
Joined
Mar 11, 2002
Messages
26
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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
I'll also note Microsoft actually has a similar version posted on their excel site, but this one is much nicer :)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top