Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switch off automatic calculation for a sheet (XP S

    Hello everybody,

    one simple question: is it possible to switch off the automatic calculation for a single sheet? I have one sheet, where recalculation needs some time (see <post#=691285>post 691285</post#>). Unfortunately, this also affects all other sheets, which were pretty fast up to now. Now I would like to switch off the calculation on the time consuming sheet until this sheet is active or any other condition becomes true.
    Is there a way to separately turn on/off the calculation? Is there any other way?
    I am looking forward to your ideas.

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

    Re: Switch off automatic calculation for a sheet (XP S

    You can set the EnableCalculation property of a worksheet to False, either manually (in the Visual Basic Editor), or using code:

    Worksheets("Test").EnableCalculation = False

    When you set EnableCalculation to True again, the worksheet will automatically be recalculated.

    Please keep in mind that if EnableCalculation is False, the sheet will not be recalculated at all, even if you press F9 or use Worksheets("Test").Calculate

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

    Re: Switch off automatic calculation for a sheet (XP S

    One other point to keep in mind: the EnableCalculation status of a sheet is not stored with the workbook. When you open a workbook, all sheets have EnableCalculation = True. You can set it to False for one or more sheets in the Workbook_Open event procedure in ThisWorkbook.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch off automatic calculation for a sheet (

    Hello Hans,

    thank you very much. That does exactly what I need.

  5. #5
    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: Switch off automatic calculation for a sheet (

    Coincidentally I was doing this yesterday using this code in the ThisWorkbook module:
    <pre>Private Sub Workbook_Open()
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
    wks.EnableCalculation = False
    Next wks
    ActiveSheet.EnableCalculation = True
    End Sub

    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.EnableCalculation = True
    End Sub

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Sh.EnableCalculation = False
    End Sub

    </pre>

    FWIW
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Germany, Germany
    Posts
    169
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switch off automatic calculation for a sheet (

    Hello Rory,

    did I already mention, that I love coincidences? That's a very good idea! Thank you very much!!

Posting Permissions

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