turning off automatic calculation when loading VBA add-in

[Lars Schouw]

I want to turn of automatic calculation in Excel from my add-in
and have put the following code in the ThisWorkbook module.

Private Sub Workbook_Open()

Application.Calculation = xlCalculationAutomatic
End Sub


When starting Excel and there by loading the add-in I get the following
error:

Method 'Calculation' of object '_Application' failed

I seems to be the problem?

Lars

 

[Lars Schouw]

Found a solution

ThisWorkbook.Activate
Application.Calculation = xlCalculationManual

because:
You need to have a workbook active before you can set the Calculation
property and although it seems counterintuitive, you can "activate"
your
add-in workbook to serve this purpose even though it's not visible.
You need to have a workbook active before you can set the Calculation
property and although it seems counterintuitive, you can "activate"
your
add-in workbook to serve this purpose even though it's not visible.

Source:http://www.excelforum.com/archive/index.php/t-260962.html

Thanks
Lars