Permanent Iterative Calculation

[Neuner]

I have several spreadsheets that require the 'Enable Iterative Calculation' to be selected in order to work correctly. Our business calcs require circular calculations on almost every sheet.

Once I have this option checked, it stays active but only for me. It does not stay checked for my co-workers and it has become very frustrating for them.

Is there a way to permanently keep the iteration calculation option active? Any programming that could run in the background to make it permanent?

Thank you!

 

[jmacleary]

Hi there. Simply put Application.Iteration = True

in wherever you want it to be forced (workbook open macro, or worksheet activate etc.). Bear in mind that this will change the users application settings (i.e. for all workbooks from then on). You may want to consider capturing the current setting in a global variable, and resetting it when your workbook is closed.

 

[Neuner]

You may want to consider capturing the current setting in a global variable, and resetting it when your workbook is closed.

Thank you John! Sorry but I'm not sure what you mean by this. I think having it reset when the workbook is closed is a good idea but I'm not sure what you mean by global variable or how to do this.

 

[jmacleary]

Hello again - no need to apologise, I should have made it clearer. Copy all the code below into your vba code in the ThisWorkbook (From the developer tab select Visual Basic then find the entry for ThisWorkbook (it will be at the end of the list of sheets).

This code will copy the current state of iteration when the workbook is opened, and reset it before the workbook is closed.

Public SaveIteration As Boolean

Private Sub Workbook_Open()
SaveIteration = Application.Iteration
Application.Iteration = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Iteration = SaveIteration

End Sub