OnCalculate Property

An OnCalculate event handler runs immediately after a worksheet is recalculated. Use the OnCalculate property of the Worksheet object to associate a procedure with the recalculation of a single, specified worksheet. Use the OnCalculate property of the Application object to associate a procedure with the recalculation of any open worksheet.

For example, you might use an OnCalculate handler to update column widths when new data is recalculated, as shown in the following example.

Sub TrapCalculate()
    Application.OnCalculate = "FitColumns"
End Sub

Sub FitColumns()
    Columns("A:H").EntireColumn.AutoFit
End Sub

If you've assigned a procedure to the OnCalculate property of the Application object, the procedure runs for any recalculated worksheet that doesn't have its own OnCalculate handler.

Note

The OnCalculate event handler doesn't run if you recalculate under program control. For example, the statement Application.Calculate recalculates all open workbooks, but it doesn't cause the OnCalculate event handler to run.