Message while macro is executing

N

Nan

My macro takes several seconds to execute. I'd like some sort of message to
pop up and distract users from the flickering screen in the background while
the macro runs. Is there a way to display a blank screen, or at least ask
users to be patient, while the continues to run? I put in a message box, but
the macro stops until the user clicks "OK".

I appreciate any suggestions and thank you for taking time to help
 
J

JW

You can use the status bar:
Application.StatusBar

To prevent the "flickering screen", turn off screen updating before
your code executes and turn it back on once the code is complete:
Application.ScreenUpdating=False
'your code here
Application.ScreenUpdating=True
 
B

Bob Umlas, Excel MVP

Manually create a text box (large) with bold 16-point font red text with your
message. Let's say it's called "Text Box 1". In VBA's immediate window,
(ctrl/G), type
activesheet.textboxes("Text box 1").visible=false
and it disappears. Now, in your macro:
Sub MyMacro()
activesheet.textboxes("Text box 1").visible=true
Application.screenupdating=false 'avoids flicker
'your regular code here
activesheet.textboxes("Text box 1").visible=false
Application.screenupdating = true
End Sub

Bob Umlas
Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top