Just a thought . . . not tested

Why not place a volatile function like NOW() in an out of the way cell on sheet2, then in the worksheet_activate module force it to recalculate which should trigger the code.

Private Sub Worksheet_Activate()
    Application.Calculate
End Sub