Permanent Iterative Calculation

Neuner

New Member
Joined
Jul 18, 2018
Messages
18
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

Well-known Member
Joined
Oct 5, 2015
Messages
832
Office Version
2007
Platform
Windows
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.
 
Last edited:

Neuner

New Member
Joined
Jul 18, 2018
Messages
18
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

Well-known Member
Joined
Oct 5, 2015
Messages
832
Office Version
2007
Platform
Windows
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.

Code:
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
 
Last edited:

Forum statistics

Threads
1,085,544
Messages
5,384,350
Members
401,888
Latest member
nisabina

Some videos you may like

This Week's Hot Topics

Top