Apr 12 2021 10:12 AM
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.
Apr 12 2021 11:31 AM
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.
Apr 12 2021 11:46 AM
Mar 30 2024 12:06 PM
@HansVogelaar I need to be able to do this without VBA since users are often disallowed from running .xlsm files at their companies.
Mar 30 2024 12:37 PM
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.