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.
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:
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.
|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