by Allen Wyatt
(last updated May 26, 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 This_Workbook 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 This_Workbook 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, and 2016.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...Discover More
You can use some of the tools in Excel to convert cell references in formulas into names. Converting back (from names ...Discover More
ScreenTips are one of those artifacts of Microsoft trying to make Excel be overly helpful. If the ScreenTips bother 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.