Use Excel Worksheet_Calculate to Print When a Formula Result Changes
Question details
Create an Excel VBA macro that prints a designated range when the result of a formula cell changes after recalculation.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to monitor formula-driven values such as cells A2 and D2 and trigger printing after worksheet recalculation.
- Observed behavior
- The Worksheet_Change event does not run when a formula result changes without a direct edit to the formula cell.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and enable macros when opening it. Place the event code in the worksheet's code module for the sheet that contains the formulas, not in a standard module.
Use Worksheet_Calculate and compare the previous formula result
Use this method when a formula result changes because of recalculation rather than a direct edit to the formula cell.
Worksheet_Change detects edits to cell contents, but it does not detect a changed result returned by a formula. Worksheet_Calculate runs whenever the worksheet recalculates, so store the last value of the monitored formula cell and print only when its current value differs.
In Excel, press Alt+F11 to open the Visual Basic Editor. In Project Explorer, double-click the specific worksheet that contains the formulas, such as Sheet1.
Paste this code into that worksheet module: Private Sub Worksheet_Calculate() Static PreviousValue As Variant Dim CurrentValue As Variant CurrentValue = Me.Range("D2").Value If PreviousValue <> CurrentValue Then If Not IsEmpty(PreviousValue) Then Me.Range("A1:D20").PrintOut End If PreviousValue = CurrentValue End If End Sub
Replace "D2" with the formula cell whose result should trigger printing. Replace "A1:D20" with the exact range to print; using Me.Range ensures that Excel prints from the worksheet containing the event code.
Return to Excel, change a cell that affects the formula in D2, and recalculate with F9 if needed. The selected range prints when D2 returns a different value.
Force a worksheet recalculation when source data is updated
Use this if dependent values do not recalculate automatically, preventing Worksheet_Calculate from running after an input change.
Work with Formula-Based Spreadsheets in WPS Office
If you need a free, lightweight alternative for spreadsheet calculation and formula-based workflows, WPS Office provides a familiar spreadsheet interface, broad Office-format compatibility, and an easy transition from Microsoft Office files.
Frequently Asked Questions
Why does Worksheet_Change not run when a formula result changes?
Worksheet_Change runs when a user or macro directly changes a cell's contents. A formula cell can display a new result after recalculation without its formula being edited, so use the worksheet's Worksheet_Calculate event instead.
Where should I put Worksheet_Calculate VBA code?
Press Alt+F11, locate the workbook in Project Explorer, and double-click the relevant worksheet under Microsoft Excel Objects. Paste the event procedure into that worksheet module.
How can I print only when D2 changes instead of after every calculation?
Store D2's previous value in a Static variable inside Worksheet_Calculate, compare it with Me.Range("D2").Value, and call PrintOut only when the two values differ.
How do I print a specific range from the worksheet event?
Use an explicit range, such as Me.Range("A1:D20").PrintOut. Replace A1:D20 with the area you want printed; Me ensures the range belongs to the worksheet that recalculated.
Why does the macro print when I open the workbook?
Opening a workbook can trigger recalculation. Initialize the previous value without printing, as shown by checking If Not IsEmpty(PreviousValue) before calling PrintOut.
