Turn On AutoCalculations but do not calculate now

[zico8]

Hi,

Is it possible to enable AutoCalculations (after I disabled its) by prevent immediate calculations until normally it would be executed (after some changes in worksheet is done?)

 

[shg]

Why not wait until you've made your changes to turn it back on?

 

[zico8]

I have quite big table with many formulas in it.
Calculating takes lot of time so I do not want to wait if table is filtered only

 

[shg]

Okay.

Why not wait until you've made your changes to turn it back on?

 

[zico8]

Could you explain what exactly do you mean?

I am preparing table for other users, generally they will be only looking at filtered data.
But sometimes after filtering it they are able to change some values. Then auto calculating should be turned on (instead of turned on immediately after filterring)

 

[shg]

You are setting calculation to Manual, correct?

Then at some point, you are setting it back to Automatic, correct?

Does that do what you want? And if not, what do you want it to do instead?

 

[ZVI]

I am preparing table for other users, generally they will be only looking at filtered data.
But sometimes after filtering it they are able to change some values. Then auto calculating should be turned on (instead of turned on immediately after filterring)

Put this code into ThisWorkbook module and try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Application.Calculation <> xlCalculationAutomatic Then
    Application.Calculation = xlCalculationAutomatic
  End If
End Sub

 

[Mentor82]

Hi,
If I understand you right you want to have calcation turned on and make only calculation automatically if any value in any cell is changed, is that right? If so, check this code in workbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Application.Calculation <> xlCalculationAutomatic Then
    Application.Calculate
  End If
End Sub

 

[shg]

If so, check this code in workbook module.

Isn't that about the same as setting calculation to automatic?

 

[Mentor82]

Application.calculate is not the same as.automatic calculation. It behaves as if you pressed F9 button which stands for one time calculation. When you put this code in the workbook as mentioned above algorism will run calculation if any change in any cell is made and when the automatic calculation is switched off. Soyou have switched off auto calculation and will have it calculated on any cell value change. Check this out.