Turn off auto calc (Excel 2003)

Topic

New Reply

I have a workbook that contains many formulas. Data entry in this workbook can be very slow because of the auto calc. I turned off the auto calc, but noticed that it it doesn’t come back on automatically with other workbooks that I use. Can anyone tell me if there is some code that would turn off the auto calc when this workbook opens and then turn it back on when the workbook is closed? I would prefer to force a calculation in this workbook as I need it.

Thanks for your help!

Reply | Quote

Replies

Hi Marie

have you tried this:

Private Sub Workbook_Open()
With Application
.Calculation = xlManual

End With

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.Calculation = xlAutomatic

End With

End Sub

Reply | Quote

Hey Jerry!

Thanks for the speedy reply.

Reply | Quote

Or even better:

Private lngCalc As Long

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

Private Sub Workbook_Deactivate()
If Not lngCalc = 0 Then
Application.Calculation = lngCalc
Else
Application.Calculation = xlCalculationAutomatic
End If
End Sub

This code goes into the ThisWorkbook module of the workbook.

Reply | Quote

Thanks Hans!

Reply | Quote