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.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
A little green triangle in the corner of a cell means that Excel thinks there is an error with the cell contents. If ...Discover More
Enter a value into a cell and "poof!" it is automatically divided by 100. What's going on, and how can you fix it so that ...Discover More
Normally the Tab key can be used to move from one cell to another in Excel. If this cell movement doesn't work for you, ...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.