OzGrid

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

< Back to Search results

 Category: [Excel]  Demo Available 

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.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

 

How to use a VBA code for clipart
How to data trim and clean cell values with VBA code
How to use VBA code to output multiple worksheets to separate workbooks
How to use VBA code to generate report based on criteria
How to use Excel VBA code to hide based on criteria

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Gallery



stars (0 Reviews)