How can I set automatic formula calculation as a worksheet feature

Question

MS Excel gives me a possibility to set a way how formulas are calculated, either automatically or manually (FILE -> OPTIONS -> FORMULAS -> Calculation options -> Workbook Calculations).

It is clear. It is MS Excel settings on each particular comp.

I create forms for other users. Many of them have MANUAL FORMULAS CALCULATION option set on their comps. It is not possible to manage/explain to everybody to have AUTOMATIC FORMULA CALCULATION set on their comp.

I somehow need to add AUTOMATIC FORMULA CALCULATION as a feature of my worksheet.

I need to arrange for an automatic formula recalculation in my worksheet even on other comps having manual formula calculation settings. Like a fool-proof provision against those form users who keep manual formula calculation settings in their Excel options.

Any idea?

Some basic settings I missed?

Developer tool?

VBA?

Thanks.

hansvogelaar · Answer

JS_questions&nbsp;
You can do the following:
Press Alt+F11 to activate the Visual Basic Editor.
Double-click ThisWorkbook under Microsoft Excel Objects in the left-hand pane.
Copy the following code into the workbook module:
Private Sub Workbook_Open()
    Application.Calculation = xlCalculationAutomatic
End Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
&nbsp;
Your users will have to allow macros when they open the workbook.

js_questions · Answer

Thank you Hans, will try and let you know. Have a nice day.

rachel murray · Answer

HansVogelaar&nbsp;I need to be able to do this without VBA since users are often disallowed from running .xlsm files at their companies.&nbsp;

hansvogelaar · Answer

Rachel Murray&nbsp;
I'm sorry, but there is no way to enforce automatic calculation without VBA.
You might place an instruction in a cell on the worksheet that tells users to turn on automatic calculation.

Forum Discussion

How can I set automatic formula calculation as a worksheet feature

Share

Resources