manual calculation only when a specific workbook is activated

[andreagiugio]

Hello,

I would like to set manual calculation when a workbook called "AMS" is activated.
The other workbooks should be always in automatic calculation.
Moreover if the workbook called "AMS" is activated and then closed and saved..the other workbooks should always be in automatic calculation.

Could you please help? I am getting mad.

Thanks!

 

[Fishboy]

Hello,

I would like to set manual calculation when a workbook called "AMS" is activated.
The other workbooks should be always in automatic calculation.
Moreover if the workbook called "AMS" is activated and then closed and saved..the other workbooks should always be in automatic calculation.

Could you please help? I am getting mad.

Thanks!

Hi andreagiugio, try this:

- Press ALT+F11 to open the VBA Developer window
- Find the name of your workbook in the Project Pane in the top left of the window
- Right-click on ThisWorkbook and select View Code
- Copy / paste in the following 3 macros:

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

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
End Sub

 

[andreagiugio]

Hi andreagiugio, try this:

- Press ALT+F11 to open the VBA Developer window
- Find the name of your workbook in the Project Pane in the top left of the window
- Right-click on ThisWorkbook and select View Code
- Copy / paste in the following 3 macros:

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

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

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Calculation = xlCalculationAutomatic
End Sub

It works!! Thanks!!!!!
just a question: what is Workbook_BeforeClose ?

 

[Fishboy]

It works!! Thanks!!!!!
just a question: what is Workbook_BeforeClose ?

Happy to help.

You said that you wanted it so that calculation goes back to automatic if that workbook gets closed, and as the name suggests it is a macro that runs when you close the workbook containing the code

The (Cancel As Boolean) part basically means that if you go to close the workbook but then choose Cancel from the SaveAs dialog box then the code does not run, meaning the calculation remains manual until you either close the workbook or make a different one active.