Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Turn Calculation on/off

Status
Not open for further replies.

miwoodar

Technical User
Dec 4, 2006
34
US
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
 
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? [ponder]
How do I get the best answers?
 
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
 
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? [ponder]
How do I get the best answers?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top