Values calculated by formulas disappear (are zeroed) on workbook window deactivate

[lossoss]

Hello excel experts,

I am intentionally setting Calculations to Manual when specific worksheet in my workbook are activated (using Worksheet_Activate event and Application.Calculation = xlCalculationManual code). As I want to ensure that once I activate other workbooks Calculations are set to Automatic. Which works perfectly fine either using Workbook_Deactivate or Workbook_WindowDeactivate event and running Application.Calculation = xlCalculationAutomatic code. The only problem is that some formulas stop working e.g. this one

=INDEX(tblLabourRates, MATCH(D5&"Cost-plus"&C5,tblLabourRates[Country]&tblLabourRates[Rate type]&tblLabourRates[Currency],0), MATCH(TEXT(E5,"0"),tblLabourRates[#Headers],0))*F5*WORKDAYHOURS(D5)

calculationsdisapear.png

Initially i though values are zeroed, which was the case but it's result of IFERROR function. I have removed IFERROR from one cell (see top right cell on the screenshot) and obviously formula above stops working.

Other fomula e.g. =IF('EFFORT ESTIMATES'!$A611<>"0",SUMIF('EFFORT ESTIMATES'!$S$609:OFFSET('EFFORT ESTIMATES'!$S$609,,rscProjectWeeks+2),'P&L'!$C$103,'EFFORT ESTIMATES'!$S611:OFFSET('EFFORT ESTIMATES'!$S611,,rscProjectWeeks+2)),0) keeps working.

Attached picture for illustration and the code which is "problematic" . If that code is not used all formulas keeps working. Thanks in advance for advice. Tomas

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.Calculation = xlCalculationAutomatic
End Sub

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[lossoss]

Thanks Pepe for having look. I was fighting with the problem further and when I added following code:

Sheet.EnableCalculation = False

to Workbook_WindowDeactivate subprocedure and it did a trick. Now values are calculated by all formulas and they stay visible when workbook deactivated. Obviously it's because sheet is not being calculated when workbook windows is not active, which is not a problem for me.

Sheet.EnableCalculation = False