VBA sub for Worksheet_Calculate is not being triggered. What triggers this event?

Question

I have a workbook and on one sheet it has the sub Worksheet_Calculate which does some formatting on the cells for the numbers, just sets the format for number of decimals to show depending on the value of that cell and another cell.

I have tried to debug the problem and turned on EnableEvents, opened my Workbook and it does its thing so far. I enter data and I expect the sub to be triggered and to hit my break point but I don't. I even click the "Calculate Now" and "Calculate Sheet" buttons, nothing. So what is supposed to fire off the Worksheet_Calculate sub? I need the workbook to do the formatting that is done in this sub, I don't really care where it does it so long as it does it. I even tried Application.EnableEvents = False at the beginning of the sub and Application.EnableEvents = True at the end of the sub, to no avail.

I don't know much about VBA or this Workbook as it's not something I created, I would have made a C# web application instead of the Excel spreadsheet but I digress.

Edit 1:

Here are the formulas/values for the cells in question to clarify things a bit. Range Q11:Q35 are all formatted to be number types. Their formula is as follows.

=IF(OR(O11="",P11=""),"",(O11/P11)*100)

The cells referenced above have the following formulas for O and P respectively.

=IF(OR(H11=""),"",INDEX(H11:L11,MATCH(9.99999999999999E+307,H11:L11))-M11)

=IF(AND(M11="",N11=""),"",N11-M11)

Use Worksheet_Change if you want to execute code after something has been changed. Worksheet_Calculate is only being executed when the worksheet calculates — gizlmo
There is a formula in the cells that Worksheet_Calculate will be changing the numerical formatting of. — DrPepperholik
If changing a cell that the formula depends on doesn't trigger the Calculate event, you either have manual calculation set, or events are disabled. — Rory
The formula in "Q" cells depend on the row's corresponding "O" and "P" cells. Initially they're blank and I type something in "O", press enter, then "P" and the formula for "Q" does its thing but the Worksheet_Calculate event never fires, I never hit my break point. It's set to automatic calculation and events are enabled. — DrPepperholik

steenbergh steenbergh · Accepted Answer · 2016-04-21T12:42:34

I've tried to use the MSDN Example found here: https://msdn.microsoft.com/en-us/library/office/ff838823.aspx

The example is nothing more than:

Private Sub Worksheet_Calculate()
    Columns("A:F").AutoFit
End Sub

It didn't trigger when I was just typing text on the sheet, but it did trigger when I input a formula: =1+1.

Make sure you're actually inputting something that Excel needs to calculate.

EDIT: As @gizlmeier mentioned in the comments, use Worksheet_Change if you want to execute code after something has been changed.

EDIT 2: To elaborate on when the Calculate is triggered:

When a formula is input that requires direct calculation
When a cell is modified that is referenced by a formula.

So, inputting this formula in column G:G does not trigger Calculate: =LEFT(Ax, 3). If we now input text or another static value into range A:A, Calculate is triggered because col G:G needs to be reworked.

VBA sub for Worksheet_Calculate is not being triggered. What triggers this event?

1 Answers