Written by Allen Wyatt (last updated February 3, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 Microsoft 365.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel allows you to make a wide range of customizations to both the Quick Access Toolbar and the ribbon. If you want to ...
Discover MoreExcel can helpfully display some statistical information in the program's Status Bar. If you cannot see all the ...
Discover MoreNormally 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-05-29 05:20:48
David Shepherd
This is one of those situations where it may be worth capturing the original status of circular referencing, and resetting on closure. So the ThisWorkbook module would look something like
Public gbStatus as Boolean
Private Sub Workbook_Open()
gbStatus = Application.Iteration
Application.Iteration = True
End Sub
Private Sub Workbook_Close()
Application.Iteration = gbStatus
End Sub
If there is the possibility of having other workbooks open which must have circular referencing disabled, you could use the Activate and Deactivate events instead of Open and Close.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments