How can I set automatic formula calculation as a worksheet feature

Copper Contributor

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.

2 Replies

@JS_questions 

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).

 

Your users will have to allow macros when they open the workbook.

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