Application.EnableCancelKey = xlDisabled

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

On Feb 22, 8:46*am, Jeffrey Marcus wrote:
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

On Feb 18, 3:24*pm, Ryan H wrote:



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"), _
* * * * * * * * * * * * * * Procedu="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.

--
Cheers,
Ryan

"Dan" wrote:
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" wrote:

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 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.
--
Cheers,
Ryan

"Dan" wrote:

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" wrote:

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

--
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -