Excel CalCulation Issue

[C.R.]

Hello and Happy New Year,

I am having a mysterious Excel Calculation issue.
Workbook Calculation is set to Automatic.
When the workbook Opens, I enable Automatic Calculation as well.

Most of the cell references on other worksheets are just simple stuff like =Data!A1.
Yet nothing updates unless I select the cell as if I was going to edit the formula, and then hit enter.
Then it will update. But I can’t really do that with 1000s of cells.

The problem sometimes goes away if I completely close Excel, then reopen.
But sometimes that doesn’t work either.

Does anyone have any ideas here?

Thanks in advance….

C.R.

[SamT]

Workbook Calculation is set to Automatic.
When the workbook Opens, I enable Automatic Calculation as well.

Where and How?

More importantly, it sounds like that there is a procedure(s) that set Calculation to Manual without resetting it. Possibly a Selection_Change Sub.

Use the VBE Menu Edit >> Find >> Find What:="Calculation" >> Search:=Current Project

[C.R.]

SamT,

Where: File>>>Options>>>Formulas>>> Workbook Calculation>>> Automatic Radio Button is selected.

How: In a ThisWorkBook Module (From The Tips and Tricks Sticky)

Private Sub Workbook_Open()
    Application.OnTime Now, "ContinueOpen"
End Sub

"ContinueOpen" is:

Sub RestoreSheetsCalculation()
  Dim sh As Worksheet
  For Each sh In Worksheets
    sh.EnableCalculation = True
  Next
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
MsgBox "Auto-Calculation Enabled."
End Sub

I've checked all the other Modules and there isn't anything that would disable Automatic Calculation.
This is what's weird here. It is an intermittent problem with a workbook that gets used, by me only, on a daily basis.

Thanks for your help.

C.R.

[Jan Karel Pieterse]

Rename "RestoreSheetsCalculation" to "ContinueOpen", otherwise the OnTime event in Workbook_Open has no macro to run.

[Paul_Hossler]

Q: Any chance that something turns off .EnableEvents? WB_Open wouldn't run I believe if Excel is already open, and then you open a WB with a WB_Open event (I have a number of WB's in which I disabled events, and the macro crashed before I could re-enable events)

Q: Can you just move the RestoreSheetsCalculation code into the WB open event without using OnTime?

Paul

[SamT]

Another thing to try is putting or calling RestoreSheetsCalculation in the Workbook_BeforeSave Event sub

[C.R.]

Sorry... I wasn't Clear above.

Continue Open Module Calls RestoreSheetsCalculation.

I will check out and try all suggestions. Yesterday everything was normal... no issues.
I'll also re-scour all the other macros in this WB for anything related to Paul's thought that something is getting turned off somewhere. This makes sense.
It still is the intermittent occurrences of this issue that has me baffled.

SamT your suggestion above in post #2 was most helpful too. The only place where "calculation" was found was in what I posted in post #3.

Many thanks guys,

C.R.

[snb]

Check:

File/Options/advanced/When calculating this workbook/Update links to other workbooks