disabling automatic calculation

[erniepoe]

Hi all, I have a huge database that I'm working with, and worksheets within the same workbook that run a lot of sumproduct formulas, so any filtering/updating within the database took a long time.


I installed the following code (in the right-click, view code) window, which I thought would turn the specific sheet into manual calculation, but also make it so that other Excel workbooks could be left in automatic calculation mode without having to physically change it back.


Is this a matter of the macro not be sophisticated enough, or am I installing/running it incorrectly? What is happening is that it still turns this and every workbook into manual calculation mode, when I only want it on one specific work book.


Thanks,
Ernie



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

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

 

[dave3009]

Calculation is an application level thing. In order to switch back on the calculation when switching between workbooks you need to use a similar code in the ThisWorkbook module using Workbook_Activate/Deactivate events

 

[erniepoe]

Thanks Dave,

So that would be a matter of just right-clicking on the tab in question, clicking on the "this workbook" thing over on the project VBA tree in the upper left, and copy-pasting the code in there?

 

[kpark91]

Yes. pretty much.

But don't forget to change the Sub names.

from Worksheet_Activate to Workbook_Activate

 

[erniepoe]

Awesome. Thanks so much, guys.

 

[erniepoe]

Also, should I change the calculation to manual in the workbook in question, or just leave it all as manual and have the macro take over?

 

[erniepoe]

hmm, it didn't really work. the calculations are still automatic even with the code installed, so it runs slowly.

if I change it back to manual calculation, it makes that change in every excel spreadsheet.


back to the drawing board, i guess....

 

[kpark91]

LOL. Ohhhh I just read your question right now -_-
I just just caught up with all the replies put on this thread..
You don't need to put the code in Workbook Module

So the the original code you were using (Worksheet_Activate) WAS CORRECT.

To speed up the calculations, an easy way to do it is to disable ScreenUpdating and enabling ScreenUpdating after the calculation is done.

so...

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

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

 

[erniepoe]

Private Sub Workbook_Activate()
Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_Deactivate()
Application.Calculation = xlCalculationAutomatic
End Sub


---------------------------


This is now the code I have in the "this workbook" module. It is correct, no?