Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoCalc Off for a file (Excel 2000)

    Is it possible thru code to turn off automatic calculation within a specific excel file. I've done it in the past while recording a macro -- but it turns off automatic calculation for all my excel files. And, I don't want that!.
    Any help would be appreciated.
    Thanks,
    --cat

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    You can add these to the workbook object:

    <pre>Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
    Application.Calculation = xlManual
    End Sub

    Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
    Application.Calculation = xlCalculationAutomatic
    End Sub</pre>


    It will toggle calc manual/auto whenver you activate/deactivate the workbook
    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    You need code for the Sheet.Activate event (for the entire Workbook) such as

    ActiveWorksheet.EnableCalculation = False

    setting it back to True on the Sheet.Deactivate event. To do this you need to select the ThisWorkbook object in the tree view of your Excel file. You should be able to find this in the top left hand window of your VB Editor - which is where you see your code after recording a macro.
    Gre

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    This can be "dangerous" to do.

    This prevents the sheet from being recalculated at all. It will not even calculate using <F9> or ctrl-alt-<F9> or even any of the VB calculate methods (sheet, application, etc).

    If you have sheets dependent on this sheet they will only be updated with the values you entered before you added the code to disable calcs.

    Steve

  5. #5
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    Thanks, Steve, this works great. My users were complaining that it took so long to "navigate" around. Turning off calculation improves this. And, it still saves with all the formulas calculated! Perfect!
    Cindy

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    Your solution is preferred. I only consider using this in tandem with Activate events. Thank you for reminding everyone of the proviso.
    Gre

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    Another thing to be aware of (I had to test to be sure)

    If you try to "reenable" the "Active" sheet on sheet deactivate, you actually will enable the sheet you just selected (the NEW active sheet), you will not reenable the old sheet. You change sheets before the event is triggered and the code is run.

    You will have to explicitly define the sheet to enable in the sheet deactivate code, you can't use "activesheet"

    Steve

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    I understood the requirement to be for a Workbook that had calculation fully disabled until it was saved. Clearly, this code is not suitable for intermediate calculations triggered by F9 etc - although saving will update the calcs. Had the query been worded otherwise; no doubt I would have responded differently.
    Gre

  9. #9
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoCalc Off for a file (Excel 2000)

    Thanks, ... I apologize ... sometimes I just can't seem to say what I mean!
    --cat

Posting Permissions

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