Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Calculation settings (2003)

    I have a workbook with two worksheets (W1 and W2). W1 contains a database for users to enter data and it has only minor calculations. W2 contains a summary page of the database and contains major calculations in it all from the data in W1. W2 is slowing down entry in W1 while auto calculation is on. I would like auto calculation to only auto calculate W1 and not W2. Is this possible? I also tried to move W2 into it's own workbook, but excel told me that the formulas were too large for system resources (or something like that). This is really a project meant for Access that I have been forced to do in Excel.

    Any thoughts or other workarounds??

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Auto Calculation settings (2003)

    Calculation settings are application wide, so you can't do exactly what you want. However, here is some VBA code which will set recalculation to manual when the data sheet is activated, and reset recalculation to automatic when the data sheet is deactivated; the effect should be to speed up the response time when the sheet is being used for data entry. On W1, right click the tab name and select View Code. On the right side, in the code window, enter this, save the file:

    Option Explicit

    Private Sub Worksheet_Activate()
    Application.Calculation = xlCalculationManual
    End Sub

    Private Sub Worksheet_Deactivate()
    Application.Calculation = xlCalculationAutomatic
    End Sub

    HTH.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Auto Calculation settings (2003)

    You can disable calculation for W2 in the Workbook_Open and Worksheet_Deactivate events and only re-enable it in the Worksheet_Activate event. The routines would look like this:

    In the module behind WS2 (right-click the sheet tab and choose 'View Code')

    <pre>Private Sub Worksheet_Activate()
    Me.EnableCalculation = True
    End Sub

    Private Sub Worksheet_Deactivate()
    Me.EnableCalculation = False
    End Sub

    </pre>


    and in the ThisWorkbook module:

    <pre>Private Sub Workbook_Open()
    Sheets("WS2 name goes here").EnableCalculation = False
    End Sub
    </pre>


    You might be better off reviewing exactly what is going on in your summary sheet first though to see if that can be improved - adjusting formulae, using helper columns etc.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Calculation settings (2003)

    Nice work Rory!! Exactly what I needed it to do!

Posting Permissions

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