The behaviour of Worksheet_Calculate event – stored events

[Kolobok]

Greetings fellow Excel users! Let me first begin by saying that this is my first post on this forum (actually on any forum), so please forgive me if I inadvertently break any rules for making posts. And I would also like to thank you for taking time out to read my post.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Now, let me describe the problem I’m having (Windows 7 Ultimate, Excel 2010) by giving you a set up. Take a Workbook with 2 Sheets. Sheet 2 Cell A1 has a formula =Sheet1!A1, EnableCalculation property of Sheet2 to TRUE and in Sheet2 Object there is a Sub
<o></o>

Private Sub Worksheet_Calculate()
    i = MsgBox("Value " & Me.Range("A1").Value, vbOKOnly)
End Sub

In Module1 there is a SUB<o></o>

Public Sub mySub()
    Application.EnableEvents = False
    ThisWorkbook.Worksheets(2).EnableCalculation = True
    ThisWorkbook.Worksheets(2).EnableCalculation = False
    Application.EnableEvents = True
End Sub

Now, enter 1 in Sheet1 Cell A1 – a message box with “Value 1” is displayed. Next, set EnableCalculation property of Sheet2 to FALSE and enter 2 in Sheet1 Cell A1 – nothing happens. Now, run the mySub in Module 1 – Cell A1 on Sheet2 displays 2 and EnableCalculation property of Sheet2 is set to FALSE. So far, so good. Now enter 3 in Sheet1 Cell A1 – a message box with “Value 2” is displayed!

It would seem that during the execution of mySub a Calculation event was fired (even though Application.EnableEvents was set to FALSE) and the value of 2 was stored (where?) and then this event was released when 3 was entered into Cell A1 on Sheet1 (even though EnableCalculation is set to FALSE for Sheet2). Any clue what’s going on here and how to “fix” this strange behaviour?
Many thanks.<o></o>
<o></o>

 

[RoryA]

The 2 comes from the value in the sheet (since it is not recalculating the formula when you enter 3) but it does appear odd that the calculate event fires again.

 

[Kolobok]

Hi, Rory. Thank you for reading and replying to my post. I don't suppose you have tried replicating my set up to see if the problem is specific to my system (Excle 2010, Windows 7 Ultimate)? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[RoryA]

It's not specific - I can replicate in both XL2010 and XL2003 on WinXP. It seems to be a bug to me (pending further investigation). As and when I get a chance, I'll file it with MS though I doubt it will get a high priority in all honesty as it is not a common scenario.

 

[Kolobok]

Ok. Thanks again. I guess I'm gonna have to look for a workaround to this.

 

[Kolobok]

Yet more strange behaviour of Worksheet_Calculate event: put
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
=Sheet1!A1<o></o>

in Sheet2, then put <o></o>

=Sheet2!A1<o></o>

in Sheet3, and set EnableCalculation property of Sheet2 to FALSE. Now, changing values in cell A1 on Sheet1 will trigger a Worksheet_Calculate event on Sheet3, even though values on both Sheet2 and Sheet1 do not change! Another bug?..<o></o>
<o> </o>

 

[Kolobok]

Hi, Rory.

I seem to have run into another strange behaviour in Excel - this time it's SetLinkOnDataMethod (see my post http://www.mrexcel.com/forum/showpost.php?p=2721711&postcount=20). If you have a moment to spare, I would appreciate if you could check if you can replicate what I'm seeing.

Many thanks.