Excel Workbook Calculation -> always automatic

[ItIsHardToProgram]

I don't know if this problem has been adressed so far, I have searched in the FAQS and on the WEB but I haven't found any hope of fix on this issue.

You probably all know that excel sets the auto calculation or manual calculation depending on the way the first worksheet opened was saved. I thought that putting automatic or manual on the personal worksheet would work, but apparantly not.

Is there a way to make sure a worksheet can't be changed to automatic or manual what ever the settings are on the initial opened worksheet?


If the only fix is a macro, will do, but I would like it to do on open and on save on the specific workbook only, maybe a OnOpen event.

Thank you for your time.


"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[AnotherHiggins]

Open up the VB Editor ([Alt] + [F11]).

Press [Ctrl] + [R] to ensure that the Project Explorer is open.

In the Project Explorer (at the far left of the screen), select ThisWorkbook under the project for the workbook you want to affect.

At the top left of the right-hand pane, select Workbook from the dropdown.

In the top right dropdown, select Open.

In the Sub Workbook_open, add this line of code:
Application.Calculation = xlAutomatic

It should look like this:

Private Sub Workbook_Open()
    Application.Calculation = xlAutomatic
End Sub

Save the workbook.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

IIHTP,

That doesn't, however, prevent the user from turning the calculation mode to manual at any time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

True and very true, but from what I can understand of the situation, it is impossible to have automatic calculation on one workbook, and manual on another when they are both opened at the same time, so I guess this is the best fix, because I don't necesserely want to block the access from Manual calculation since it is useful when we pull reports from our DB.

Thanks all.

Is there a reason why Microsoft imbeded this behavior in excel?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[AnotherHiggins]

-> it is useful when we pull reports from our DB.

Do you use macros to pull the reports? If so, control calculation status within the macro, but always reset to Automatic before exiting the macro.

If you aren't using macros, you could start.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ItIsHardToProgram]

No obviously do not use macros to pull reports to excel files, MSDE/SQL database, its another story.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.