by Allen Wyatt
(last updated December 10, 2018)
Ron has a workbook that requires the use of circular references, which he can configure Excel for just fine. After protecting and e-mailing the workbook to colleagues, upon their use, the iterative capability (required for circular references) is turned off and the worksheet fails due to circular-reference errors. Ron wonders if there is a way to default the workbook so that circular references are enabled when it is loaded by his colleagues.
The only way to make sure that the colleagues' workbooks have circular references enabled is to add a macro to your workbook. The macro is actually only one line long, and you'll want to make sure you add it to the ThisWorkbook module:
Private Sub Workbook_Open() Application.Iteration = True End Sub
The macro runs every time the workbook is opened, and it turns on the circular references setting.
There are a couple of things to remember when it comes to having this actually work for your colleagues. First, your workbook will need to be saved in a "macro enabled" version, meaning it will have the extension XLSM. If your colleagues disable macros—either explicitly when opening the workbook or implicitly through the Security Center settings they have set up in Excel—then the macro may not run when the workbook is opened. In such situations, these colleagues will still get the circular-reference errors.
The second thing to remember is that enabling the circular-reference setting (either through this macro or by doing so manually) will affect not just calculations on your workbook, but on any workbook your colleagues may have open. This shouldn't cause a huge problem, but it is still a good thing to keep in mind.
You may also want to add a macro to turn off the circular-reference setting when your workbook is closed. This, too, should be added to the ThisWorkbook module:
Private Sub Workbook_Close() Application.Iteration = False End Sub
This macro should actually be considered optional, and you may want to consider if you really want to include it or not. If your colleagues normally work with the circular-reference setting enabled, then the Workbook_Open macro won't really mess with how they use Excel. However, if your Workbook_Close macro is encountered, it will turn off the circular-reference setting and may interfere with how they use any other workbooks that require circular references.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13532) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Different cultures have different conventions for displaying numbers and for parameters in Excel's worksheet functions. ...Discover More
Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off ...Discover More
Want to load a particular add-in for use with a specific worksheet? Here's a quick way to do it using macros.Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.