disabling automatic calculation

erniepoe

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

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

dave3009

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

ADVERTISEMENT

Awesome. Thanks so much, guys.
 

erniepoe

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

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
328
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?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,460
Messages
5,511,508
Members
408,853
Latest member
JoshuaHudsonpTi45

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top