software

I have a large file which I open frequently, and it drives me crazy if I
forget to turn off automatic calculation, as it can take 10 minutes to
calculate - I usually crash out of Excel to stop it.

I added code to switch calculation to manual as the workbook opens, but
unfortunately, that workbook_open procedure seems to fire after the workbook
has been through the calculation process. Is there any code that runs
immediately when a file is opened?

Thanks in advance
DanielHi Daniel,

there's an easy way - without any programming...

Simply load the file and (unfortunately you'll have to wait this time till'
it has calculated - have you every tried to hit quot;Escquot; during calculation?)

Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
In the Project Explorer you'll see the sheets of your workbook.
Select the one which has these tremendous calculations.
Now you should see in the properties of the sheet the property
quot;EnableCalculationquot;.
Set this to False and you're done.

Now the sheet only calculates if you hit quot;F9quot;...

Hope this helps and best wishes,
Ericquot;Daniel Bonallackquot; wrote:

gt; I have a large file which I open frequently, and it drives me crazy if I
gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; calculate - I usually crash out of Excel to stop it.
gt;
gt; I added code to switch calculation to manual as the workbook opens, but
gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; has been through the calculation process. Is there any code that runs
gt; immediately when a file is opened?
gt;
gt; Thanks in advance
gt; Daniel
gt;

Wow - a fabulous solution! Thanks!

quot;Eric_MUCquot; wrote:

gt; Hi Daniel,
gt;
gt; there's an easy way - without any programming...
gt;
gt; Simply load the file and (unfortunately you'll have to wait this time till'
gt; it has calculated - have you every tried to hit quot;Escquot; during calculation?)
gt;
gt; Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
gt; In the Project Explorer you'll see the sheets of your workbook.
gt; Select the one which has these tremendous calculations.
gt; Now you should see in the properties of the sheet the property
gt; quot;EnableCalculationquot;.
gt; Set this to False and you're done.
gt;
gt; Now the sheet only calculates if you hit quot;F9quot;...
gt;
gt; Hope this helps and best wishes,
gt; Eric
gt;
gt;
gt; quot;Daniel Bonallackquot; wrote:
gt;
gt; gt; I have a large file which I open frequently, and it drives me crazy if I
gt; gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; gt; calculate - I usually crash out of Excel to stop it.
gt; gt;
gt; gt; I added code to switch calculation to manual as the workbook opens, but
gt; gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; gt; has been through the calculation process. Is there any code that runs
gt; gt; immediately when a file is opened?
gt; gt;
gt; gt; Thanks in advance
gt; gt; Daniel
gt; gt;

You sure that it calculates when you hit F9??

From VBA's help for the .enablecalculation property:

When the value of this property is False, you cannot request a recalculation.
When you change the value from False to True, Excel recalculates the worksheet.
Eric_MUC wrote:
gt;
gt; Hi Daniel,
gt;
gt; there's an easy way - without any programming...
gt;
gt; Simply load the file and (unfortunately you'll have to wait this time till'
gt; it has calculated - have you every tried to hit quot;Escquot; during calculation?)
gt;
gt; Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
gt; In the Project Explorer you'll see the sheets of your workbook.
gt; Select the one which has these tremendous calculations.
gt; Now you should see in the properties of the sheet the property
gt; quot;EnableCalculationquot;.
gt; Set this to False and you're done.
gt;
gt; Now the sheet only calculates if you hit quot;F9quot;...
gt;
gt; Hope this helps and best wishes,
gt; Eric
gt;
gt; quot;Daniel Bonallackquot; wrote:
gt;
gt; gt; I have a large file which I open frequently, and it drives me crazy if I
gt; gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; gt; calculate - I usually crash out of Excel to stop it.
gt; gt;
gt; gt; I added code to switch calculation to manual as the workbook opens, but
gt; gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; gt; has been through the calculation process. Is there any code that runs
gt; gt; immediately when a file is opened?
gt; gt;
gt; gt; Thanks in advance
gt; gt; Daniel
gt; gt;

--

Dave Peterson

I think I'd use a helper workbook that turns off calculation, then loads your
real file.

Daniel Bonallack wrote:
gt;
gt; I have a large file which I open frequently, and it drives me crazy if I
gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; calculate - I usually crash out of Excel to stop it.
gt;
gt; I added code to switch calculation to manual as the workbook opens, but
gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; has been through the calculation process. Is there any code that runs
gt; immediately when a file is opened?
gt;
gt; Thanks in advance
gt; Daniel

--

Dave Peterson

Hi Dave,

I tried it - it does....at least in my MS Excel...

Best wishes,
Ericquot;Dave Petersonquot; wrote:

gt; You sure that it calculates when you hit F9??
gt;
gt; From VBA's help for the .enablecalculation property:
gt;
gt; When the value of this property is False, you cannot request a recalculation.
gt; When you change the value from False to True, Excel recalculates the worksheet.
gt;
gt;
gt;
gt; Eric_MUC wrote:
gt; gt;
gt; gt; Hi Daniel,
gt; gt;
gt; gt; there's an easy way - without any programming...
gt; gt;
gt; gt; Simply load the file and (unfortunately you'll have to wait this time till'
gt; gt; it has calculated - have you every tried to hit quot;Escquot; during calculation?)
gt; gt;
gt; gt; Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
gt; gt; In the Project Explorer you'll see the sheets of your workbook.
gt; gt; Select the one which has these tremendous calculations.
gt; gt; Now you should see in the properties of the sheet the property
gt; gt; quot;EnableCalculationquot;.
gt; gt; Set this to False and you're done.
gt; gt;
gt; gt; Now the sheet only calculates if you hit quot;F9quot;...
gt; gt;
gt; gt; Hope this helps and best wishes,
gt; gt; Eric
gt; gt;
gt; gt; quot;Daniel Bonallackquot; wrote:
gt; gt;
gt; gt; gt; I have a large file which I open frequently, and it drives me crazy if I
gt; gt; gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; gt; gt; calculate - I usually crash out of Excel to stop it.
gt; gt; gt;
gt; gt; gt; I added code to switch calculation to manual as the workbook opens, but
gt; gt; gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; gt; gt; has been through the calculation process. Is there any code that runs
gt; gt; gt; immediately when a file is opened?
gt; gt; gt;
gt; gt; gt; Thanks in advance
gt; gt; gt; Daniel
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Hi Dave,

to make sure I'm not wrong I re-tried it and this is very strange:
Some calculations are done - some not.... e.g. quot;SUMquot; is not calculated while
quot;Nowquot; is calculated when hitting F9.

OK...to ensure calculation is possible, I'd add either a button on the
worksheet or assign a macro to a shortcut to make it calculate (by setting
worksheet.enablecalculation=True and after calculation again to quot;Falsequot;.
This would enable me to calculate the sheet whenever I (and not Excel )
want to but I'd be sure to use False as a standard.

Best wishes,
Ericquot;Dave Petersonquot; wrote:

gt; You sure that it calculates when you hit F9??
gt;
gt; From VBA's help for the .enablecalculation property:
gt;
gt; When the value of this property is False, you cannot request a recalculation.
gt; When you change the value from False to True, Excel recalculates the worksheet.
gt;
gt;
gt;
gt; Eric_MUC wrote:
gt; gt;
gt; gt; Hi Daniel,
gt; gt;
gt; gt; there's an easy way - without any programming...
gt; gt;
gt; gt; Simply load the file and (unfortunately you'll have to wait this time till'
gt; gt; it has calculated - have you every tried to hit quot;Escquot; during calculation?)
gt; gt;
gt; gt; Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
gt; gt; In the Project Explorer you'll see the sheets of your workbook.
gt; gt; Select the one which has these tremendous calculations.
gt; gt; Now you should see in the properties of the sheet the property
gt; gt; quot;EnableCalculationquot;.
gt; gt; Set this to False and you're done.
gt; gt;
gt; gt; Now the sheet only calculates if you hit quot;F9quot;...
gt; gt;
gt; gt; Hope this helps and best wishes,
gt; gt; Eric
gt; gt;
gt; gt; quot;Daniel Bonallackquot; wrote:
gt; gt;
gt; gt; gt; I have a large file which I open frequently, and it drives me crazy if I
gt; gt; gt; forget to turn off automatic calculation, as it can take 10 minutes to
gt; gt; gt; calculate - I usually crash out of Excel to stop it.
gt; gt; gt;
gt; gt; gt; I added code to switch calculation to manual as the workbook opens, but
gt; gt; gt; unfortunately, that workbook_open procedure seems to fire after the workbook
gt; gt; gt; has been through the calculation process. Is there any code that runs
gt; gt; gt; immediately when a file is opened?
gt; gt; gt;
gt; gt; gt; Thanks in advance
gt; gt; gt; Daniel
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;


Eric_MUC Wrote:
gt; Hi Daniel,
gt;
gt; there's an easy way - without any programming...
gt;
gt; Simply load the file and (unfortunately you'll have to wait this time
gt; till'
gt; it has calculated - have you every tried to hit quot;Escquot; during
gt; calculation?)
gt;
gt; Now, you select quot;Toolsquot; - quot;Macrosquot; - quot;Visual Basic-Editorquot;.
gt; In the Project Explorer you'll see the sheets of your workbook.
gt; Select the one which has these tremendous calculations.
gt; Now you should see in the properties of the sheet the property
gt; quot;EnableCalculationquot;.
gt; Set this to False and you're done.
gt;
gt; Now the sheet only calculates if you hit quot;F9quot;...
gt;
gt; Hope this helps and best wishes,
gt; Eric
gt;
gt;
gt; quot;Daniel Bonallackquot; wrote:
gt;
gt; gt; I have a large file which I open frequently, and it drives me crazy
gt; if I
gt; gt; forget to turn off automatic calculation, as it can take 10 minutes
gt; to
gt; gt; calculate - I usually crash out of Excel to stop it.
gt; gt;
gt; gt; I added code to switch calculation to manual as the workbook opens,
gt; but
gt; gt; unfortunately, that workbook_open procedure seems to fire after the
gt; workbook
gt; gt; has been through the calculation process. Is there any code that
gt; runs
gt; gt; immediately when a file is opened?
gt; gt;
gt; gt; Thanks in advance
gt; gt; Daniel
gt; gt;

HI I´m trying to use option of setting enablecalculation to false, but
when I reopen the document this property is set to true again. I´m
saving the document after setting enable calculation to false, but it
doesn´t work.--
RSantos
------------------------------------------------------------------------
RSantos's Profile: www.excelforum.com/member.php...oamp;userid=31240
View this thread: www.excelforum.com/showthread...hreadid=511965