Excel VBA Worksheet_Calculate event fires for different workbook

Additional information (should make sense after reading the rest): If I open Workbook1.xlsm, then create a new workbook (Book1), I can enter data and formulas, even volatile functions like NOW(), in Book1!Sheet1 without triggering an instance-wide recalculation. I know this because I'm watching the calculate event handler in Workbook1!SheetX.

Tentative conclusion: Excel does not always automatically recalculate all formulas in all open workbooks. Rather, it only recalculates the active sheet (or maybe the active workbook) until something happens.

In this case, "something" is actually hiding the rows in the calculate event handler. I eliminated various lines of code and found that if I remove the Me.Rows(x).Hidden = True lines, the instance-wide recalculation doesn't happen. Applying a filter is an easy way to trigger the event, but adding a formula will produce the same result without using the filter, so it doesn't seem to have anything to do with the filters.

Anyway, hiding the rows somehow creates a condition that causes everything to recalculate. Only after the macro hides the rows in SheetX is the calculate macro in SheetX executed, and after that, it executes no matter which workbook or worksheet I enter data in. If I then open a third workbook through Windows Explorer, any changes there immediately fire the calculate macro. I can add an event handler in Sheet1 and verify that both handlers (SheetX and Sheet1) are executing, and SheetX executes first every time, even though the change was in Sheet1.

Caveat: Please don't get distracted by easier or better ways to hide rows or cells. I'm sure there are. My question has to do with what seems like a bug in Excel, so please focus on the firing of events unless the problem is related to the way the macro is written.

Situation:

Using Excel 2013 under Win7.

Workbook1, SheetX has VBA macro Worksheet_Calculate(). SheetX has data filtering on.

Open Workbook1.

Open a new workbook using File | New | Blank workbook. Its name is Book2.

Go back to Workbook1 and filter a column in SheetX (say, display only values of 3 in column A). [You could instead enter another formula anywhere in the range A4:C7.]

Enter a value into a cell in Book2.

Workbook1's Worksheet_Calculate() event fires. This can be demonstrated by setting a breakpoint.

Also, after the event fires, the TAB key no longer works in Book2 but works fine in Workbook1. This seems to be specific to the sheet being displayed, because if you create a Sheet2 in Book2, the TAB key will work until you enter something in Sheet2, after which it will work in Sheet1 until you enter something there. And vice versa ad nauseam.

SheetX contains the following. The macro's sole purpose is to hide rows 2 and 3, which in real life contain parameters that the user doesn't need to know about. When a filter is used, the rows otherwise become unhidden/visible. The formula in A3 is needed to be sure the Worksheet_Calculate event fires whenever anything in the worksheet changes.

    A      B      C 
1  data1 data2 data3    
2   
3  =A2      
4  3     A3     C5  
5  4     a3     C6  
6  3     A5     C7  
7  6     a6     C8

The macro is here:

Private Sub Worksheet_Calculate()

    'Hide rows 2 & 3, which are the rows containing parameters.
    'This is necessary because whenever an autofilter selection is removed,
    ' Excel redisplays hidden rows and rows with 0 height.

    On Error GoTo errHdlr

    'First ensure that the event does not loop.
    Application.EnableEvents = False

    'Have to unprotect and re-protect the sheet to make these changes.
    Me.Unprotect

    Me.Rows(2).Hidden = True
    Me.Rows(3).Hidden = True

    'Have to specify that the user can insert hyperlinks
    ' and use filters.
    Me.Protect , , , , , , , , , , True, , , , True

    'Now restore event handling.
    Application.EnableEvents = True

    Exit Sub

errHdlr:
    MsgBox "Error #" & Err.Number & ":" _
        & vbCrLf & "  " & Err.Description _
        & vbCrLf & "Please inform tech support.", vbCritical + vbOKOnly, _
        "Worksheet_Calculate"

    Resume Next

End Sub

Because I was a little leery of the "Me" object, I checked with the debugger, and when I was in Book2, this is what I found when the event fired:

?me.Name

SheetX

?me.Parent.name

Workbook1.xlsm

?activesheet.name

Sheet1

?activesheet.parent.name

Book2

So clearly the "Me" object refers to SheetX in Workbook1, even though the active sheet is Sheet1 in Book2.

Thanks for any thoughts!

Solution

application.caller event wouldn't help here, the only work around would be to have some specific text line "My Report123" in any cell, for example Cell #A1.

When you call Worksheet_Calculate() event the first line should be the checkpoint.

if activesheet.range("A1")<>"My Report123" then exit sub

This would solve the issue of the excel bug.