Cannot reset Application Calculation status to Automatic

[ammage]

Excel 2010 VBA

In order to make the code below run faster, I set the Application to manually calculate formulas. At the end of the code, I turn it back to automatic. However, in the status bar on the spreadsheet it still shows "Ready Calculate". When I hover over "Calculate" is says "Results of formulas may be out of date because Excel is not set to calculate automatically. Click here or press F9 to recalculate manually."

Also, Formulas --> Calculation --> Calculation Options is already set to Automatic.

I have tried executing Application.Calculation = xlCalculationAutomatic from the immediate window with no effect.

I cannot seem to turn the automatic calculation back on.

I saw in an earlier post a recommendation to review this cite: Excel Status Bar shows 'Calculate' - Decision Models. None of those conditions apply

Thanks in advance for any help

Sub OverlappingProps()


  Dim cell As Object
  Dim cell2 As Object
  Dim iRows As Integer
  Dim iOR As Integer    'holds Overlap Replacement col value
  
  Application.Calculation = xlManual
  
  iRows = Range(Sheet3.Cells(3, 12), Sheet3.Cells(3, 12).End(xlDown)).Count
  
  'Find "Overlap Replacement" col number
  iOR = 1
  For Each cell In Range(Sheet3.Cells(3, 140), Sheet3.Cells(3, 140).End(xlToRight))
    If cell.Value = "Overlap Replacement" Then
      iOR = cell.Column
    End If
  Next cell
  If iOR = 1 Then
    iOR = Application.InputBox("Select Overlap Replacement cell.", "Cannot find 'Overlap Replacement'", Type:=8).Column
  End If
    
  'find and replace duplicate invoices
  For Each cell In Range(Sheet3.Cells(4, iOR), Sheet3.Cells(iRows, iOR)) 'column EL "Overlap Replacement"
    If InStr(1, LCase(cell), "overlap") > 0 Then
      'loop through row containing "overlap"
      For Each cell2 In Range(Sheet3.Cells(cell.Row, 22), Sheet3.Cells(cell.Row, iOR - 1)) 'range = row in the earn out table
        'if the cell > 0 and the cell above it is > 0 and the column heading is not "Total" then replace cell
        If cell2 > 0 And cell2.Offset(-1, 0) > 0 And InStr(1, Sheet3.Cells(3, cell2.Column), "Total") = 0 Then
          cell2 = 0
        End If
      Next cell2
    End If
  Next cell
    
  Application.Calculation = xlCalculationAutomatic
End Sub

 

[AlphaFrog]

I'm wondering if the status bar is giving you bad info when the automatic calculations have actually turned back on?

Suggested test: After you run your macro and then if you change a precedent cell value that is used elsewhere in a formula, does that formula automatically update?

One other suggestion is to force a re-calculation after you turn on Automatic Calculation.

  Application.Calculation = xlCalculationAutomatic
  [COLOR=#ff0000]Application.Calculate[/COLOR]

 

[ammage]

Thanks AlphaFrog. I confirmed that the autocalculate feature was not active. Yes, I can add the application.calculation = xlCalculationAutomatic at the end of the code, but I would rather restore the application state to calculate automatically so I can continue working in the application without having to constantly hit F9 and without having to close and reopen the application in order to reset it to calculate automatically.

However, for some reason this code solved the issue??

Sub Main()
  Dim SaveCalculationStatus As String
  
  SaveCalculationStatus = Application.Calculation
  Application.Calculation = xlCalculationManual
  
  Call PasteValues
  Call OverlappingProps
  
  Application.Calculation = SaveCalculationStatus
  
End Sub