VBA xlCalculationManual trips error when called App_WorkbookOpen

[jscranton]

I am trying to change the application settings to xlCalculationManual when opening up a workbook. However, this code throws an error if a workbook is already set to xlCalculationManual (Error: Method 'Calculation' of object'_Application' failed). It does not throw the error if the setting is xlCalculationAutomatic

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

    Application.Calculation = xlCalculationManual

End Sub

I tried instead to add a condition in another routine but I am getting very strange results. The "Change Settings" macro runs fine except when called by the WorkbookOpen procedure. Then I get a type mismatch error. If it matters, all of this is part of an excel add-in for an end user.

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

    Call changeSettings
End Sub
_____
Sub changeSettings()
 
 If Application.Calculation <> xlCalculationManual Then Application.Calculation = xlCalculationManual
 If Application.CalculateBeforeSave = True Then Application.CalculateBeforeSave = False


End Sub

 

[jscranton]

Solved.

Apparently, you have to change the setting here:

Private Sub Workbook_Open()
        Set App = Application
	Application.Calculation = xlCalculationManual
	Application.CalculateBeforeSave = False
End Sub

Rather than in the "Private Sub App_WorkbookOpen" module.

I am sure someone can explain why but wanted to close this in case future users had the same issue.