Is there a way I can display a message while a macro runs in the background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it to
stay open until the macro finishes. Any ideas?

I think there is an easier and more efficient way... but I've actually done
this before. However keep in mind this is very ghetto in the world of excel.

What I did was create a worksheet that had a message on it about not
touching anything until the screen went away.

I hide that worksheet.

Then when I run the code I open it up, activate it... and on the next line I
type

Application.ScreenUpdating = False

Activate the original sheet here...
Put the rest of your code here.

Hide the Message Sheet

Application.ScreenUpdating = True

End Sub

So what's happening is, the execution opens up that message sheet, then it
runs the rest of the code with out updating the screen until it is finished.

"Greg H." wrote:

Is there a way I can display a message while a macro runs in the background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it to
stay open until the macro finishes. Any ideas?

One way where you use a textbox that says "My macro is running, pls be
patient"

Sub showmsg()
With Sheets("sheet6")
..Shapes("Text Box 3").Visible = True
'your code here
..Shapes("Text Box 3").Visible = False
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg H." wrote in message
...
Is there a way I can display a message while a macro runs in the
background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it
to
stay open until the macro finishes. Any ideas?

On May 7, 2:58 pm, Greg H. wrote:
Is there a way I can display a message while a macro runs in the background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it to
stay open until the macro finishes. Any ideas?

Excel is only single threaded, so you can only have one thing going on
at a time, but to accomplish what you want you could add a user form
to your project. Then you macro could have the following code:

Sub warningMacro()
Load UserForm1
UserForm1.Show
End Sub

In your form, you would have the following event which automatically
starts running when the form is activated and does all your
calculations. When it is done, you just close the form.

Private Sub UserForm_Activate()
'Do whatever you need to do here. For example, wait for 10
seconds. (You have to add a label to the form for this to work)

UserForm1.Label1.Caption = "hi"
startTime = Timer

Do
DoEvents
UserForm1.Label1.Caption = (Timer - startTime)
Loop While Timer < startTime + 10

'when you are done, close the form

Unload UserForm1
End Sub


Maybe this will work for you.

All the suggestions will work but I personally like to write minimal code and
use the features available in the language instead of reinventing my own
methods. I use
A userform with VBModeless option, which adds only two lines to your
existing macro. First Insert a userform in your project ( I usually reduce
its size to the caption area and change the caption from userform1 to
Processing.... , this way I create a floating message ...)
Sub mycode()
Userform1.Show (vbModeless) <---- first line of your existing code
..
..
your code goes here


Unload Userform1 <----- last line of your existing code
End Sub

--
Best regards,
Edward


"Don Guillett" wrote:

One way where you use a textbox that says "My macro is running, pls be
patient"

Sub showmsg()
With Sheets("sheet6")
..Shapes("Text Box 3").Visible = True
'your code here
..Shapes("Text Box 3").Visible = False
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg H." wrote in message
...
Is there a way I can display a message while a macro runs in the
background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it
to
stay open until the macro finishes. Any ideas?

Thanks for the code ... I used yours and added Application.ScreenUpdating =
False to keep the sheet I want it on. The only problem now is it shows the
outline of the form but its white. I have text on the form but its not
displaying. This is the first time I have used a form so I am lost in what
to do. Thanks

"Edward" wrote:

All the suggestions will work but I personally like to write minimal code and
use the features available in the language instead of reinventing my own
methods. I use
A userform with VBModeless option, which adds only two lines to your
existing macro. First Insert a userform in your project ( I usually reduce
its size to the caption area and change the caption from userform1 to
Processing.... , this way I create a floating message ...)
Sub mycode()
Userform1.Show (vbModeless) <---- first line of your existing code
.
.
your code goes here


Unload Userform1 <----- last line of your existing code
End Sub

--
Best regards,
Edward


"Don Guillett" wrote:

One way where you use a textbox that says "My macro is running, pls be
patient"

Sub showmsg()
With Sheets("sheet6")
..Shapes("Text Box 3").Visible = True
'your code here
..Shapes("Text Box 3").Visible = False
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg H." wrote in message
...
Is there a way I can display a message while a macro runs in the
background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it
to
stay open until the macro finishes. Any ideas?

If only thing you want to display is a short mesage just type it as Form's
Caption in its properties ... . You don't need Application.Screen
updating=false when you use a modeles userform so just remove it and it will
show your text.
--
Best regards,
Edward


"Greg H." wrote:

Thanks for the code ... I used yours and added Application.ScreenUpdating =
False to keep the sheet I want it on. The only problem now is it shows the
outline of the form but its white. I have text on the form but its not
displaying. This is the first time I have used a form so I am lost in what
to do. Thanks

"Edward" wrote:

All the suggestions will work but I personally like to write minimal code and
use the features available in the language instead of reinventing my own
methods. I use
A userform with VBModeless option, which adds only two lines to your
existing macro. First Insert a userform in your project ( I usually reduce
its size to the caption area and change the caption from userform1 to
Processing.... , this way I create a floating message ...)
Sub mycode()
Userform1.Show (vbModeless) <---- first line of your existing code
.
.
your code goes here


Unload Userform1 <----- last line of your existing code
End Sub

--
Best regards,
Edward


"Don Guillett" wrote:

One way where you use a textbox that says "My macro is running, pls be
patient"

Sub showmsg()
With Sheets("sheet6")
..Shapes("Text Box 3").Visible = True
'your code here
..Shapes("Text Box 3").Visible = False
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Greg H." wrote in message
...
Is there a way I can display a message while a macro runs in the
background?
I want to let users know to wait until the macro finishes. I tried the
msgbox command but the macro will not run until you press ok and I want it
to
stay open until the macro finishes. Any ideas?