Written by Allen Wyatt (last updated March 6, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
David has a need, in a worksheet, to turn off automatic calculation when the worksheet is opened. Then, he needs to limit the number of times that the worksheet can be calculated (using F9) to a maximum of 3 times.
It is possible to do, using macros, on a workbook basis. All you need to do is to have the macro turn off automatic calculation and then run some code each time calculation occurs. Start by adding this single line to a general module in the workbook:
Global iCalcCount As Integer
Since this line does not appear within a procedure, it defines a variable that will be available globally. It will be used to keep count of the number of times calculation occurs in the workbook.
Now you need to add three macros (all event handlers) to the ThisWorkbook module:
Private Sub Workbook_Open() Application.Calculation = xlManual Application.CalculateBeforeSave = False End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object) iCalcCount = iCalcCount + 1 If iCalcCount > 2 Then Application.OnKey "{F9}", "" MsgBox "You have already done 3 Calculations since opening." & _ vbCrLf & "{F9} is now disabled" Exit Sub End If End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.Calculation = xlAutomatic Application.CalculateBeforeSave = True Application.OnKey "{F9}" End Sub
Two of these macros fire when the workbook is opened and when it is closed. In the Workbook_Open macro, the two code lines turn off automatic calculation and also turn off the calculation that normally occurs whenever the workbook is saved. (This is necessary so that one of the user's "allowed" recalculations doesn't occur by mistake, in saving the workbook.) These configuration settings are undone when the Workbook_BeforeClose event handler is executed.
The workhorse in this approach is the Workbook_SheetCalculate event handler. This is executed, automatically, whenever the worksheet is recalculated. Since automatic recalculation and recalculation when saving have been turned off, this means that the Workbook_SheetCalculate event only occurs when the user does something to force calculation, such as pressing F9 or clicking on a tool that recalculates. Even if the user manually turns on automatic recalculation, the Workbook_SheetCalculate event will still trigger.
The Workbook_SheetCalculate event increments the iCalcCount counter and if it is greater then 2, it then uses the .OnKey method to disable F9. Of course, the user can still use one of the built-in tools to try to recalculate (such as the Calculate tool on the Status Bar), but that still would not result in the worksheet being recalculated.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13831) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Excel allows you to change the names assigned to the worksheets in a workbook. If you want to have those names appear in ...
Discover MoreExcel makes it relatively easy to copy worksheets to a different workbook. That doesn't mean it couldn't be made simpler ...
Discover MoreSorting worksheet tabs can be done by using a macro. This tip provides a macro that accomplishes this task, but it also ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-03-06 10:02:24
David Goulding
Excellent response to my 'cry for help'!
For interest, I use it in friends and family Karaoke sessions to 'force' singers out of their comfort zone! Limiting the options to 3 prevents us from continuing until we're 'comfortable' again! (see Figure 1 below)
Singers then learn a chosen track from the selected artist to sing at the next session. (see Figure 2 below)
It works so well... and lots of fun!
Thanks to all for their help on this.
David (UK)
Figure 1. Choose an Artist
Figure 2. Choose a track
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 © 2024 Sharon Parq Associates, Inc.
Comments