Ken Wright :toggle autocalc, workbook vs. worksheet

  • Thread starter Thread starter drabbacs
  • Start date Start date
D

drabbacs

Ken (or anyone with an answer),

I saw a reply you made to an inquiry about turning off
autocalc for individual sheets within a workbook.
Timestamp for your message on the group is March 9 11:28
AM.

Related question. Is it possible to turn off autocalc for
workbook A and leave autocalc on for workbooks B, C, D
etc.

I have a calculation intensive workbook that remains open.
Meanwhile I will have other excel files that are opened
and closed throughout the day. Eachtime these other files
are updated, excel also tries to update the big file,
which slows everything down.

I am semi comfortable with VBA. That is, I've dabbled but
am still learning.

Thanks for any help,
Drabbacs
 
Hi
autocalculation in the options menu is a workbook level setting. so
just disable this for your specific workbook. The other workbooks will
remain untouched by this
 
Hi Drabbacs,

Franc is right about autocalculation.
However if you are persistent in wanting this there is a possibility.
Each sheet in Excel has an Enablecalculation property.

I consider it to be a VERY dangerous property. It is only accessible via
VB and indeed has the effect that the sheet is no longer recalculated.
After saving and opening the file the property is reset to TRUE, but the
sheet isn't (yet) recalculated.
You will have to force a recalculation of the sheet (<CNTR>+<ALT>+F9 , or
change some cell on the sheet through which another cell changes).
I don't like this possibility, but if you want you can toggle this option
(for all sheets) with the subroutine below.

Sub ToggleDisable()
Dim Ws As Worksheet
For Each Ws In Worksheets
Ws.EnableCalculation = Not Ws.EnableCalculation
Next
End Sub


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Hi Frank, Drabbacs

Sorry, I mentioned you (Frank) were right by mentioning that this setting is
at filelevel, however as far as I know it is NOT (completely) right.
Autocalculation is (again : as far as I know) an applicationlevel setting.
That is the problem Drabbacs has : if he changes it to automatic, all the
workbooks (including the one he doesn't want (yet) to recalculate) are
recalculated.

The setting IS however saved with the file.
That is ( I'm not sure here) the setting which is active at the time the
workbook is saved.

Whether the setting is manual/automatic the next time the file is opened is
( haven't tested it yet, but read it last week) dependent of the setting of
the first file which was opened (or of course the setting "arrived at" by a
manual change after that opening).

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top