Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Turn off auto calc (Excel 2003)

    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!

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Turn off auto calc (Excel 2003)

    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
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Turn off auto calc (Excel 2003)

    Or even better:
    <code>
    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
    </code>
    This code goes into the ThisWorkbook module of the workbook.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off auto calc (Excel 2003)

    Hey Jerry!

    Thanks for the speedy reply.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Turn off auto calc (Excel 2003)

    Thanks Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •