Auto-Enabling/Disabling Auto Calculation

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
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...
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Feb 18, 2002
Messages
165
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

Well-known Member
Joined
Oct 16, 2002
Messages
657
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

Board Regular
Joined
Oct 23, 2002
Messages
67

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Oct 23, 2002
Messages
67

ADVERTISEMENT

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

Legend
Joined
Feb 18, 2002
Messages
13,690
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

Board Regular
Joined
Oct 23, 2002
Messages
67
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,653
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top