You may also want to try this. Let me know if this helps, if so, click "YES"
below.

Put this in the worksheet that calculations are taking place.

Private Sub Worksheet_Calculate()
UserForm1.Show
End Sub

Put this in the userfrom module.

Private Sub UserForm_Activate()
Call WaitTimer
End Sub

Put this code in a standard module.

Sub WaitTimer()

If Application.CalculationState < xlDone Then
Application.OnTime EarliestTime:=Now + TimeValue("00:00:01"), _
Procedu="WaitTimer", Schedule:=True
Else
Unload UserForm1
End If

End Sub
--
Cheers,
Ryan


"Dan" wrote:

Hi Ryan,

Thanks for the reply - I appreciate it. The reason behind my original
question is a pretty sizable model that I have built around a couple of data
sources that link to a data warehouse (downloaded into Pivot and data tables
within the model). The model takes raw data from numerous sources and
combines it into a simple user-friendly analytical format that the user can
manipulate via simple drop-downs. Given the amount of data involved, a
couple of the user selections on the Dashboard tab require about 10-20
seconds for Excel to calculate. However, users are sometimes too impatient
and will start clicking on the worksheet if the calc is not instantaneous,
which will interrupt the calculation process. Hence, my desire to have a
window pop-up that informs the user that calculation is going on and to be
patient (the Excel status bar in the lower-right hand corner is not obvious
enough). I have discovered the following VBA code (see below) that initiates
a pop-up window upon calculation, however, I cannot get the window to
disappear once calculation stops (have tried a few IF THEN statements, but in
vain) - the only way to get rid of it with the code below is to manually
close it, which i do not want the user to have to do...

Private Sub Worksheet_Calculate()
msg = "Calculating...Please Wait"
MsgBox msg
Do
Loop Until Application.CalculationState = xlDone '
End Sub

Is there a way to get the message box to automatically go away once
calculation has stopped?

Also, I have created a UserForm (UserForm1) to replace the message box (so I
can format it), however, i cannot get the message to show-up in the user
form, only the UserForm itself - can you help?

Thanks in advance!

Dan

"Ryan H" wrote:

Excel should not take but a second to calculate everything. I'm not sure why
you want to do what you are wanting to do. Do you have a macro that is
taking a long time to run? If so, here is a tip to speed up your macro.

Sub YourMacro()

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.StatusBar = "Excel is still calculating...Please Wait."
End With

' your code here

With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.StatusBar = False
End With

End Sub

Note: You can't easily use a MsgBox to tell the user Excel is still
calculating. But you can change the text in the Status Bar in the lower left
corner in Excel.

Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan


"Dan" wrote:

Hi - I am new to VBA and am trying to figure out how to get a message box to
pop-up whenever Excel calculates so the user waits and does not interrupt the
calculation process. Specifically, it would remain on the screen until the
calculation process stops.

Thanks!