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

[Gwenifah]

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.

[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

 

[RoryA]

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:

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:

autoCalcForWorkbook ThisWorkbook, False

and recalculate/re-enable calculation using:

autoCalcForWorkbook ThisWorkbook, True

or just

autoCalcForWorkbook ThisWorkbook

since the default option is True.

 

[Gwenifah]

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:

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

 

[RoryA]

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.

 

[Gwenifah]

Understood. I will give it another try.

Thanks Rory!