|I hate to even suggest this, because you shouldn't have to do this. You should find (or at least eliminate) the root cause of the problem, even if that takes a re-install of Office.|
To force calculation to Automatic, we have to go back to my earlier statement: Excel will follow the "instructions" of the first workbook that is opened in any given Excel session.
Therefore, you would need to open a workbook that is set to Automatic before any other workbooks are open, including a new workbook. The only way I know how to do this is to create a personal.xls workbook, store it in the XLSTART folder so that Excel can find it and then place the code below in the ThisWorkbook module of the personal.xls workbook.
Since Excel will look in the XLSTART folder and open personal.xls first if it finds one, the code will run as soon as Excel opens the file, forcing personal.xls to be Automatic and any subsequent workbooks that open should follow along.
Of course, I have to add this disclaimer: If whatever is going on changes the setting after the workbooks are opened, then this solution isn't going to work. The code will force personal.xls to Automatic everytime, but it's not going to prevent anything that happens after the file is open.
Note: You could put this code in the ThisWorkbook module of every existing workbook to deal with each workbook individually, but once again, you shouldn't have to do this at all. And, once again, if the setting is being changed sometime after the workbook is opened, forcing Automatic on Open is only going to help up until time the setting gets changed.
Private Sub Workbook_Open()
Application.Calculation = xlCalculationAutomatic