Prevent excel from updating inactive worksheets ?

[Morphil]

hello,
I have built an Excel file which can be considered as :

2 data base on 2 separate sheets :
1rst (item descriptor) defining the known characteristics of items
2nd (stock) saying how many of such items are in stock

1 input formular on 1 other sheet

2 output formulars on 2 other sheets

The I/O formulars allow to define new items, add/remove items in stock ... IN OTHER WORDS, they are linked to the 2 data bases through many SUMPRODUCT functions. The formulars run also VBA macros to check coherency before inputting/outputting/creating ... items

All this works fine BUT SLOWLY !
What I noticed is the following : if I simply REMOVE the 2 Output formulars sheets (when using the input one) execution is Much quicker. WELL, if I am a SINGLE user, and want ONLY to use one of the formulars at a time, I don't care that Excel spends time upgating cells on NON ACTIVE sheets ! Of course when switching from Input to Output formular (in other words changing the active sheet) I fully understand/accept some extra processin (updating) time

SO, to summarize, IS THERE A WAY TO PREVENT EXCEL FROM UPDATING INACTIVE WORKSHEETS ?

br

 

[Taul]

you could have a workbook open event to turn sheet2 to manual calculation,
also
sheet1 "worksheet activate" to turn sheet2 to manual calculation
sheet2 "worksheet activate" to turn sheet2 to automatic calculation

 

[Taul]

maybe something like this

Private Sub Worksheet_Activate()

' Automatic calculation for this sheet is turned off by default
' when the page tab is clicked, the calculation is turned ON
' and calculates the page (for updates) and then turns the calculation to Manual
' This is so the spreadsheet works faster and repeated re-calculations do not
' slow down the overall performance of the spreadsheet

With ActiveSheet
    .EnableCalculation = True
    .UsedRange.Calculate
    .EnableCalculation = False
End With
End Sub

 

[Taul]

almost forgot
in the "ThisWorkbook" module put this

Private Sub Workbook_Open() 'sheet2 will be manual cal when wkbook opens

' This turns off the Automatic calculation for the sheets listed below.
' Each sheet's calculation is turned ON temporarily when the 
' sheet tab is clicked by the worksheet activate event

Sheet2.EnableCalculation = False 

End Sub

 

[Morphil]

Hi Paul,
This looks exactly what I needed/expected
And awfully ... simple
Thanks a lot !

 

[mikerickson]

If all the cells in a sheet are inactive, copy/paste special values will replace the formulas on that page, leaving the data. Which will make the formulas on the other sheets calculate faster.

 

[Morphil]

Hi Mike,
Thanks. but I already played that card ...
BR

 

[Morphil]

Hi Paul,
It's really A M A Z I N G !!!
it did benchmark before and after your suggestion :
before : 6 I/O per minute only
after : 150 I/O per minute
I can hardly believe it

Thanks, Thanks Thanks a lot
Best regards

 

[Taul]

Such is the beauty of a public access forum.
I got most my code from this site in the first palce.