Setting calculation mode via a macro

[Dean]

This is similar to a post of a few days ago which seems to have garnered zero attention, so I will simplify:

When I open up one particular file (which has data tables), I would like a macro to immediately tell EXCEL that I want to temporarily alter my normal default setting which is Tools - Options - Calculation - Automatic and, instead, use Tools - Options - Calculation - Automatic Without Data Tables.

Can this EXCEL setting be changed by a macro that automatically launches when this file is opened?

Thank you very much!
Dean

 

[Die_Another_Day]

Use an OnOpen Macro with this line of code:
Application.Calculation = xlSemiautomatic

HTH

Charles Chickering

 

[Dean]

Yes, this is what I tried and posted about 3 days ago in a post entitled
"Execute Upon Opening". I used the coding you have described. Strangely,
that same file that did not work then, seems to be working now! My only
remaining question, for now, is that:

Upon opening this file, it seems to update the data tables (as evidenced by
the lower right showing "Table 1", then "Table 2...". After it is done, the
workbook does show as being automatic except data tables, as I like.
Assuming it is recalcing these tables upon opening, is there any way to stop
it from doing so; or does it need to do this just once to establish some
sort of initial workbook integrity?

Also, if the user does want to recalc the data tables every once in a while,
is there a key one can hit that does this one time only, without resetting
the setting.

Thank you Charles!
Dean

 

[Die_Another_Day]

I think you can use F9 to recalculate, but I'm not sure if that will
recalc the tables as well. I think Shift+F9 is supposed to recalc all.
I don't know if you can stop the calculation upon opening.

 

[Dean]

It looks like F9 does recalc all, including tables, and shift-F9 simply
recalcs the current worksheet.

Does anyone else know if there is a way to avoid calc'ing the data tables
upon initial opening? You would think it would not need to since it
probably recalcs them upon closing/saving.

Thanks, DAD.