Message while macro is executing
 

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
--
TIA, Nan

Message while macro is executing
 

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

Nan wrote:
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
--
TIA, Nan

Message while macro is executing
 

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

"Nan" wrote:

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
--
TIA, Nan

Message while macro is executing
 

Thank you so much! This is perfect!
--
TIA, Nan


"Bob Umlas, Excel MVP" wrote:

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

"Nan" wrote:

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
--
TIA, Nan