Disable Calculation of selected worksheets toggle

[Floss]

I have been reading through prior posts for some code to disable calculation
process on a select sheet, so far no luck. So I have to ask for help on this
one.

I have a large worksheet that produces various reports from common data
which really slows the calculation to 3 min. I would love to be able to
disable calculation on some worksheets altogether when I dont need them and
reenable when necessary. Thus, it would really be nice to add the following
controls

code for a toggle button to turn calculation off for a specific worksheet
display the status whether the worksheet is disabled or enabled

I was hoping to create a "Control" worksheet tab that would list all tabs
with toggle device (button) next to it and then worksheet status somewhere
next to the worksheet name.

This is different then what I see others have posted on auto vs manual calc
macros. I am not sure if what i need is possible but so far this community
has always led me to a solution.

If there is solution can someone please provide some example to code this
for one or two worksheets please? I am a novice at this but can generally
follow the logic but weak on syntax.

Thank you as always,
Floss

 

[Charles Williams]

You can use worksheet.enablecalculation to achieve this.

See http://www.decisionmodels.com/calcsecretsh.htm for some more details.

There are some downsides: notably that when you re-enable calculation for a
sheet Excel will do a full calculation, and the enablecalculation setting is
not stored in a saved workbook.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[scott]

I would like to see anything you come up with. Sounds very useful.

Scott