VBA message box to inform user that Excel is still calculating

[Dan]

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!

 

[Ryan H]

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.

 

[Dan]

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]

You should be able to accomplish what you are looking for using a Userform.
I will assume you just have a Label on the userform that says, "Excel is
still calculating...Please Wait." or something like that. Since I can't
duplicate your pivot table situation I was not able to test this code. So
let me know if you have any issues. Please give specifics of the errors if
any. Let me know if this helps, click "YES" below.

Put this in the worksheet that is being calculated. This code will show the
userform when the worksheet beings to calculate.

Private Sub Worksheet_Calculate()
UserForm1.Show
End Sub

Put this in the userform module. This code will fire when the userform is
shown. It will continue the loop until Excel is done calculating.

Private Sub UserForm_Activate()

Do
If Application.CalculationState = xlDone Then
Unload UserForm1
Exit Do
End If
Loop

End Sub

 

[Ryan H]

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"), _
Procedure:="WaitTimer", Schedule:=True
Else
Unload UserForm1
End If

End Sub

 

[Dan]

Hi Ryan,

Thanks again for all of your help with this - I do have a follow-up though:

The macro that you wrote worked great except that it only fires once
calculation is complete (so the window pops-up just after calculation stops).
My goal is to get the window to pop-up once calculation starts and then go
away after it stops so the user doesn't start clicking on the worksheet and
interrupt the calc process. Is there some way to start the userform when
calculation state = xlpending or xlcalculating? and then have it stop once
calculation is complete?

Again - I appreciate all of your time!

Thanks,

Dan

 

[Ryan H]

Excel must be calculating before it shows the userform. I'm not sure if we
can stop that. Give this a try. This set of code will turn off calculation
mode, show your userform, calculate, then unload the userform (in theory).

Private Sub Worksheet_Calculate()
Application.Calculation = xlCalculationManual
UserForm1.Show
End Sub

Private Sub UserForm_Initialize()

Application.Calculate
Do
If Application.CalculationState = xlDone Then
Application.Calculation = xlCalculationAutomatic
Unload UserForm1
Exit Do
End If
Loop

End Sub

If this doesn't work send me an e-mail to (e-mail address removed) with your
workbook and I will take a look at it. I may be able to turn off calculation
mode to the workbook and take advantage of some pivot tabel events. Hope
this helps! If so, let me know, click "YES" below.

 

[Dan]

Hi Ryan,

My workbook will calc, then show the UserForm, then calc again...and again.
I have to manually get rid of the UserForm (however, Excel continues to
calc). There are a few worksheets in the workbook with formulas, so I'm not
sure if that makes a difference.

I really can't send you the file as it contains confidential info. Unless
you have any more suggestions, I guess that I'll have to throw in the towel
on this one. I'll check back just in case, but if you want to move onto
other posts, then I'll say thanks for all of your time.

I'm also not sure if you get points when people hit the 'yes', but I will as
a thanks for your time & effort.

Thanks again,

Dan

 

[Ryan H]

Since I can't see your situation I am left to guessing. Give this situation
a try.

1. Set Calculate Mode to Manual for the Workbook.
2. Put this code in the worksheet module the pivot table is located. I
assume the name of your pivot table is "PivotTable1". This code will fire
each time the pivot table is updated.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
If Target.Name = "PivotTable1" Then UserForm1.Show
End Sub

3. Put this code in the Userform module. The code will now manually
calculate your workbooks. And Excel will test if it is still calculating
every 1 second. If its not, it should unload the userform.

Private Sub UserForm_Initialize()
Application.Calculate
Call WaitTimer
End Sub

Private Sub WaitTimer()

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

End Sub

4. And yes, I guess you could say we get points. I have helped over 100
people so I get this nice little silver medal next to my name. The next step
is a gold medal, but I must help 500 people. Hope this helps! If so, let me
know, click "YES" below.

 

[Jeffrey Marcus]

Hi, I am having a similar issue.

I want to prevent the user from interrumpting the
"application.calculate" command.

Auto calc is off to start, in the button, I set it to manual and save
the current calc mode state. Throughout the subroutnie in the button
I execute application.calcualte several times and they can take a few
minutes. The issues that if the user click the sheet during this
application.calculate the application stops the calculate and
continues to the next statement.

Dan, Did you ever settle on a good fix for this?

Thanks,
Jeff

 

[Jeffrey Marcus]

Application.EnableCancelKey = xlDisabled

oh yes, a glorious day. I stumbled upon this and it works!