disabling automatic calculation

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Active Member
Joined
Oct 23, 2006
Messages
330
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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
Yes. pretty much.

But don't forget to change the Sub names.

from Worksheet_Activate to Workbook_Activate
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330

ADVERTISEMENT

Awesome. Thanks so much, guys.
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
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

Active Member
Joined
Oct 23, 2006
Messages
330

ADVERTISEMENT

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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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...

Code:
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

Active Member
Joined
Oct 23, 2006
Messages
330
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,132,680
Messages
5,654,716
Members
418,149
Latest member
tjanok

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