How to use VBA code to check iternations in the formula bar

How to use VBA code to check iterations in the formula bar

Requirement:

Is there a VBA code you can set so that when someone opens an Excel file it will automatically check the iterations in the formula bar to 1000, even if another Excel doc is open that does not have that checked. As well as prevent the warning (circular reference or otherwise) from popping up?

Also this is just for this particular workbook.

Solution:

Any change you make to the formula calculation options will affect all open workbooks. We can try to do a workaround by setting our code to "turn on" when our workbook is active, and "turn off" when deactivated. The other caution is that ti disable the circular error msg, we'll have to disable ALL error messages. This may/may not be a good thing, but I'll include it for now. To install, right-click on your sheet tab, view code. In the top left, double click on the ThisWorkbook module. In the area that opens up, paste this code:

Code:

Private Sub Workbook_Activate()
'Turn off alerts, set iteration, enter manual calculation mode
    With Application
        .DisplayAlerts = False
        .Iteration = True
        .MaxIterations = 1000
        .Calculation = xlCalculationManual
    End With
End Sub


Private Sub Workbook_Deactivate()
'If our workbook is not active, reset formula settings
With Application
    .DisplayAlerts = True
    .Iteration = False
    .MaxIterations = 100
    .Calculation = xlAutomatic
End With
End Sub

Close the Visual Basic Editor (VBE), and your workbook should be ready. Again, be mindful of what these changes are doing.

Obtained from the OzGrid Help Forum.

Solution provided by Luke M.