Turn Calculation on/off

[miwoodar]

I have users who are working in very large workbooks. The books take forever to recalculate due to a large number of offset formulas. To help speed things up I have included and auto-open macro that turns automatic calculation off. The users are happy with this solution. The problem is this - When they open excel to work in other unrelated workbooks the manual calculation remains.

What is the best way to handle this? Should I just turn auto-calc on when they close one of the large books? What if they have many of the large books open and only close one of them? Can excel turn manual calculation on for individual books (i.e. only the aforementioned large ones) but have all other books remain in auto calculation?

____________________
Mike

 

[Loomah]

the immediate thing that comes to mind is to use the workbook's activate and deactivate events to change the settings.

but i think that will probably calculate your large books too as calculation is a property of the application not the workbook (i think!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[miwoodar]

Is there a way to detect if the user has selected to end the application versus just closing a book? If yes - could I check to see which the user has chosen and turn auto-calc on if they have closed the entire application? Would this be a good approach?

____________________
Mike

 

[Loomah]

ok, i might be wrong about this (it's been a very very long time since i did anything vaguely interesting in vba) but the application events probably require you to create a class event for the application (don't know if i said theat right!)

have a look at combos series of faqs on events. once the application event is created i still don't know if it has a before_close event.

that said creating the event for the application seems a little overkill.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​