Calculation on/off event

[Guest]

I have a worksheet called "All Data" (in Excel 2003) with a huge data range
(about 45,000 cells) that is accessed in whole by many dozens of formulas on
other sheets in the workbook. I have reviewed Charles Williams' excellent
primer in improving Excel performance, but what I think I need to do is have
Calculation automatically turn off when the user hits "All Data" and turned
back on when the user leaves "All Data". Is there any way to do that?

While that would resolve all the repeated, slow recalculation that takes
place whenever a velue on All Data is changed, the one thing it would do is
prevent updates within All Data from occurring. I can instruct the user to
hit F9 on occasion in order to refresh a data validation and all the
conditional formatting witin All Data, but what would be better - under the
scenario that Calculation is turned off - is for just the All Data sheet
itself to recalculate "internally" whenever a change is made. Is that
possible?

TIA

 

[Guest]

I generally include
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

at the beginning of code and


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

at the end to speed up execution.

 

[Guest]

Thanks, Barb. Can I tie those into a specific worksheet so they set and
reset the calculation mode upon entering the worksheet and then exiting it?

 

[Gord Dibben]

Private Sub Worksheet_Activate()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
End Sub

Private Sub Worksheet_Deactivate()
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Right-click on the "All Data" tab and select "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks to both of you. I see that this approach causes only changed cells on
the affected worksheet to be updated. This does help a lot, although I'd be
interested in any way to have the entire worksheet get recalculated every
time there is a change. Is this possible? I have conditional formatting
that flags errors based on values in other cells, so these flags are not
activated under the current approach. I thought setting that
"ScreenUpdating" to "True" might do it, but apparently not.

Thanks again, this has already helped.

 

[Bill Renaud]

Add this event handler to the code module for your "All Data" worksheet (in
addition to the other 2 event handlers previously mentioned):

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Calculate
End Sub

 

[Guest]

Thanks, Bill. That worked great.

Is there any way to name one additional worksheet that I would like to have
updated when the active sheet is recalculated?

 

[Bill Renaud]

Try this (untested; substitute your actual worksheet name for "Sample"):

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Parent.Calculate
Worksheets("Sample").Calculate
End Sub

 

[Eddy Stan]

Hi
Recalculation required when sheet "x" value is changed
Recalculation required in "x" sheet when its relative cell is changed in "y"
sheet

and should not calculate when a,b,c... sheets are changed, where there no
relation between x & y sheet
will the modules given will help on that.