Vba: How to specify manual calculation on selected Open-Workbook only?

Gwenifah

New Member
Joined
Oct 2, 2017
Messages
3
Firstly, thanks for your help in advanced. I am a newbie in this forum and am a basic macro editor. My apologies if any of my post / codes doesn't make sense.

I am currently working on a report which has huge amount of raw data and formula referencing to multiple worksheets. The report is being shared internally and with customer. Due to the size of the file, it causes the report to calculate on every single click. I have set the calculation mode to manual via macro (as shown below) and included a REFRESH button (range("Forecast").calculate) which calculates the worksheet when it is clicked by the user.

Code:
[INDENT]Private Sub Workbook_Open()[/INDENT]
[INDENT]   Application.Calculation = xlManual[/INDENT]
[INDENT]End Sub[/INDENT]
[INDENT]
[/INDENT]
[INDENT]Private Sub Workbook_BeforeClose(Cancel As Boolean)[/INDENT]
[INDENT]   Application.CalculateBeforeSave = False[/INDENT]
[INDENT]   Application.Calculation = xlAutomatic[/INDENT]
[INDENT]End Sub
[/INDENT]

This works fine if one workbook is opened. However, if multiple workbook are open at the same time, it sets all workbook calculation to manual too which is not ideal. Is there any way that i can specify the workbook's name that i wish to be on manual calculation? I could not find any solution and am hoping that any of the experts here are able to help. I do know that I can set the calculation to manual without vba but my report has to be smart but yet "dummy-proof" as my users are extremely basic excel user.

Looking forward to your help and tips.

Thank you once again
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the forum.

You can disable calculation for individual worksheets, so you'd have to do that for every sheet in the workbook. For example:

Code:
Sub autoCalcForWorkbook(wb As Workbook, Optional bCalcOn As Boolean = True)
    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        ws.EnableCalculation = bCalcOn
    Next ws
End Sub

You can then disable calculation for the workbook with the code using:

Code:
autoCalcForWorkbook ThisWorkbook, False

and recalculate/re-enable calculation using:

Code:
autoCalcForWorkbook ThisWorkbook, True

or just

Code:
autoCalcForWorkbook ThisWorkbook

since the default option is True.
 
Last edited:
Upvote 0
Hi Rory,

Thanks for your help and apologies for the late response from me as I got caught up with other stuff.

Anyway, I have tried the codes but it seems to calculate the whole workbook when any changes is made. I am not sure if i am doing right or wrong.


This is what I did. Example worksheet1 which has four pivot tables:

Code:
Sub autoCalcForWorkbook(wb As Workbook, Optional bCalcOn As Boolean = True)


    Dim ws As Worksheet
    
    For Each ws In wb.Worksheets
        ws.EnableCalculation = bCalcOn
    Next ws
    
End Sub



Sub Calculate_SummaryPT1()
    autoCalcForWorkbook ThisWorkbook, True
    Range("SummaryPT1").Calculate
End Sub

Calculate_SummaryPT1() is assigned to a refresh button. Once, user changes one of the pivot filter, they will need to click on the refresh button. This refresh button will then calculate the defined range but it seems to calculate the whole workbook. Did I do it wrongly?

Also, you mentioned that I can then disable calculation for the workbook with this code: "autoCalcForWorkbook ThisWorkbook, False". Where exactly should I put it? In the workbook_open()?

Please let me know if the above is not clear.

Thank you


 
Upvote 0
The code sets the calculation mode for the entire workbook, since that's what you asked for. When you switch from manual to automatic it will calculate the whole workbook. If you only want to set a specific sheet to manual or auto, you'd need to amend the code to refer to the sheet by name instead of looping through all the sheets.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top