Auto-Enabling/Disabling Auto Calculation

[bonzo]

I have an Excel spreadsheet that has a lot graphs that are build from data that is built with a lot of INDIRECT() calls. As a result, it takes a few minutes for all of the resolution/calculation to complete. Now, since auto-calculation is the default setting, if someone wants to view my spreadsheet (including me), opening the file takes a long time. Since it's my file, I know to turn on manual calculation first and then open the file -- but my colleagues don't always know or remember that.

So, I'm looking for a way (I presume via macros) to turn on manual calculation whenever the file is opened, somehow notify the person opening the file that manual calculation has been turned on, and then do the reverse whenever the file is exited (i.e. turn auto-calculation back on and notify them that this has been done).

Can this be done? If so, any pointers on how? Also, if I have multiple workbooks open, is there a way for auto-calculation to be on for some of the files and manual calculation to be on only for this particular spreadsheet?

Thanks...

 

[lenze]

In the before open event of ThisWorkBook use this code to disable autocalculation

Application.Calculation=xlCalculationManual

USe this to reset it to automatic in the before close event

Application.Calculation=xlCalculationAutomatic

Use a message box to inform the user of the status

 

[JohnG]

Try this

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

Private Sub Workbook_Open()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
End Sub

 

[Chas17]

Private Sub Workbook_Open()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
MsgBox "Manual Calculation is ""ON"""
End Sub


You may have to adjust this for charts vice workbooks...

 

[bonzo]

On 2002-10-24 13:24, JohnG wrote:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
End Sub

When I try to exit the file (e.g. by clicking on the "X" in one the window frame), automatic is restored and it starts calculating even though all I did was view the workbook; I made no changes. Can I prevent this?

 

[lenze]

You can add this line to the before close event as the first line

If ActiveWorkbook.Saved =True Then Exit Sub

This will cause the file to close before the recalcuation is reset to automatic when no changes have been made

 

[bonzo]

On 2002-10-24 14:38, lenze wrote:
You can add this line to the before close event as the first line

If ActiveWorkbook.Saved =True Then Exit Sub

This will cause the file to close before the recalcuation is reset to automatic when no changes have been made

We're getting there; I appreciate your help. I'm still having the calculation problem. Looking at the help information on the BeforeClose event, it says this:

Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.

Changing the application settings must be triggering a need to save. My macro looks like this now:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.CalculateBeforeSave = False
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
MsgBox "Automatic Calculation has been ""enabled""."
End Sub

I'm assuming that as soon as "xlAutomatic" is restored, it starts auto-calculating. After I get my message box, I then get a message box asking me if I want to save my changes.

Hmmm...any other ideas?

 

[lenze]

The Open event is causing the SAVED property to reset to FALSE. Place this code as the last line in the Open Event (The one that sets the calculation to manual)

ActiveWorkBook.Saved=True

Now put the "If ActiveWorkBook.Saved =True Then Exit Sub" in the Before Close module(2nd line)

This should work

 

[bonzo]

On 2002-10-24 16:35, lenze wrote:
The Open event is causing the SAVED property to reset to FALSE. Place this code as the last line in the Open Event (The one that sets the calculation to manual)

ActiveWorkBook.Saved=True

Now put the "If ActiveWorkBook.Saved =True Then Exit Sub" in the Before Close module(2nd line)

This should work

Yep, that works -- except for one small problem...automatic calculation doesn't get restored -- which was the whole purpose of that sub

There's another event (WorkbookBeforeClose) whose description sounds more like what I want:

Occurs immediately before any open workbook closes.

but the example they give doesn't work and hence I can't get it work either. The example they supply is as follows:

Private Sub App_WorkbookBeforeClose(ByVal Wb as Workbook, _
Cancel as Boolean)
a = MsgBox("Do you really want to close the workbook?", _
vbYesNo)
If a = vbNo Then Cancel = True
End Sub

Thanks for continuing to try...

 

[lenze]

What's the point of restoring auto-calculation if no changes were made, as you're going to disable it again when the book is opened?

Edit: I didn't think the problem through clearly. I see what your concern would be. I'll have to think about a workaround
This message was edited by lenze on 2002-10-24 17:07