Order of Event Firing with Automatic Calculation

[mwieder]

We noticed that if Automatic Calculation is enabled and data is
entered into a cell followed by the "Enter" key, the sequence is that
the SheetCalculate event is fired first and then the
SheetChange event. Wheres if the user clicks a different
cell after entering the data (instead of hitting "Enter") the order is
reversed with the SheetCalc event only firing after the
SheetChange event.
We need to handle the input to write back to some storage, but when
the user is hitting "Enter" the data is retrieved from the storage
first since the CalcHandler fires first and hence we lose the value
the user entered in that cell. Is there some way inside the
SheetCalculate handler for us to differentiate whether there is a
SheetChange event that is about to be called? Is there some other
suggestion as to how we can handle this case?
thanks!

 

[Peter T]

Hmm, not straightforward!

First thought was to compare target & selection addresses, if not same
suggests the user clicked another cell whilst in edit mode, with the
implication the event order is Change > Calc.

But respective addresses are also not same if user presses say an arrow key
(& various others) whilst in edit mode. But the event order is the same as
Enter key, ie Calc > Change, ie your "SheetChange event is about to be
called" after Calc.

Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.

This needs a lot of testing, I've haven't, but already I see problems that
needs sorting, eg:

- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag

- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to determine 'real'
changes.

- Undo ?

Thinking about it, even if the all above can be fixed I wouldn't be at all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !

Regards,
Peter T

 

[mwieder]

Peter - first off, thanks for acknowledging and attempting to solve
this challenging Excel issue. If we can just solve it so it handles
the Enter key and the case wher eyou click of the sale after data
entry, I'll worry about any other cases. But I can't even solve
those!
The idea of comparing Target and selection doesn't work because the
value of Target is already changed when the SheetChange event gets
fired and the new value entered is lost.
We have to keep the behavior since that is how it used to work when we
didn't support AutoCalc and now that we are going to support AutoCalc
and ManualCalc, we have to handle this case.
If anyone else is up to the challenge please help!
thanks.

 

[mwieder]

Peter - first off, thanks for acknowledging and attempting to solve
this challenging Excel issue. If we can just solve it so it handles
the Enter key and the case where you click off the cell after data
entry, I'll worry about any other cases. But I can't even solve
those!
The idea of comparing Target and Selection doesn't work because the
value of Target is already changed when the SheetChange event gets
fired and the new value entered is lost.
We have to keep the behavior since that is how it used to work when we
didn't support AutoCalc and now that we are going to support AutoCalc
and ManualCalc, we have to handle this case.
If anyone else is up to the challenge please help!
thanks.

 

[Peter T]

How strange, seems I forgot to post the event routines in my previous post!
Annoyingly I didn't keep a copy of the code, will try and recreate later.

The idea of comparing Target and selection doesn't work because the
value of Target is already changed

I had in mind to compare Target & selection addresses, which may or may not
be the same depending on how the edit was completed.

Whilst it the code I worked out previously went a long way to establishing
what you wanted to know, it was far from foolproof.

Regards,
Peter T

 

[Peter T]

OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -

[from first post]
Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.

This needs a lot of testing, I've haven't, but already I see problems that
needs sorting, eg:

- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag

- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to determine 'real'
changes.

- Undo ?

Thinking about it, even if the all above can be fixed I wouldn't be at all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !

' worksheet module code

Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer

Dim mbFlag As Boolean


' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell

Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Debug.Print "Calculate, Change event will follow = " & Not mbFlag

If mbFlag Then Debug.Print
mbFlag = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long

On Error GoTo errExit
mbFlag = False

If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then

ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If

For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if required
Exit For
End If
Next

mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub


'Undo' is setting the flag the 'wrong' way but I think can work around to
correct, but if code is doing stuff to clear the undo stack that won't be
required.

Regards,
Peter T

 

[mwieder]

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!

OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -

[from first post]
Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.

This needs a lot of testing, I've haven't, but already I see problems that
needs sorting, eg:

- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag

- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to determine 'real'
changes.

- Undo ?

Thinking about it, even if the all above can be fixed I wouldn't be at all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !

' worksheet module code

Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer

Dim mbFlag As Boolean

' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell

Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Debug.Print "Calculate, Change event will follow = " & Not mbFlag

If mbFlag Then Debug.Print
mbFlag = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long

On Error GoTo errExit
mbFlag = False

If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then

ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If

For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if required
Exit For
End If
Next

mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub

'Undo' is setting the flag the 'wrong' way but I think can work around to
correct, but if code is doing stuff to clear the undo stack that won't be
required.

Regards,
Peter T

 

[Peter T]

Just try the code as-is in the ThisWorkbook module of a new workbook.

Put the cursor in SetUpTest and run F5
Change cells in A1:A10 to trigger a calculation due to changing formula
result in B1

My debug results:

for most edit changes, including complete with Enter, or a press of any of
the 'move' keys gives debug pair-
Calculate, Change event will follow = True
Change, Calculate will follow = False

type new value, exit edit mode by clicking another cell gives debug pair-
Change, Calculate will follow = True
Calculate, Change event will follow = False

IOW seems predicate the order of the subsequent event as you asked.
But as I mentioned, the caveats for a few scenarios need to be sorted out.

I see you are handling the Windows API GetKeyState
in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed.

In light testing I almost always found the API correctly returns the 'move'
keypress (if there was one), I assume as the change event fires before the
key is released. I'm not sure it would if the key is released very fast or
if the Calculate event will take a long time to process your other code. But
even if it does that shouldn't matter, I think. If the calc event fires
before the change event, mbFlag will be false which signifies the change
event is about to follow. I'll leave that for you to test.

Anyway, for me it seems to be working reasonably well, but sadly 'reasonably
well' does not of course mean reliably in all scenarios. Whilst I've no
doubt it can be improved I'm not confident it can be made bullet-proof!

Regards,
Peter T

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!

OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -

[from first post]
Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.

This needs a lot of testing, I've haven't, but already I see problems that
needs sorting, eg:

- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag

- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to determine 'real'
changes.

- Undo ?

Thinking about it, even if the all above can be fixed I wouldn't be at all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !

' worksheet module code

Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer

Dim mbFlag As Boolean

' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell

Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Debug.Print "Calculate, Change event will follow = " & Not mbFlag

If mbFlag Then Debug.Print
mbFlag = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long

On Error GoTo errExit
mbFlag = False

If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then

ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If

For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if required
Exit For
End If
Next

mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub

'Undo' is setting the flag the 'wrong' way but I think can work around to
correct, but if code is doing stuff to clear the undo stack that won't be
required.

Regards,
Peter T

 

[mwieder]

Thanks for explaining! Here's what is not clear. You state:
"If the calc event fires before the change event, mbFlag will be false
which signifies the change
event is about to follow."
If I simply hit F9, then the sheetcalc event gets fired and mbFlag is
false, but no change event is going to follow since there was no data
entry.
At this point don't worry about all the different scenarios - I just
need to differentiate between the following 3 cases which cause a
calc:
1) F9
2) data entry is done followed by enter
3) data entry is done followed by a mouse clicking away

Just try the code as-is in the ThisWorkbook module of a new workbook.

Put the cursor in SetUpTest and run F5
Change cells in A1:A10 to trigger a calculation due to changing formula
result in B1

My debug results:

for most edit changes, including complete with Enter, or a press of any of
the 'move' keys gives debug pair-
Calculate, Change event will follow = True
Change, Calculate will follow = False

type new value, exit edit mode by clicking another cell gives debug pair-
Change, Calculate will follow = True
Calculate, Change event will follow = False

IOW seems predicate the order of the subsequent event as you asked.
But as I mentioned, the caveats for a few scenarios need to be sorted out.

I see you are handling the Windows API GetKeyState
in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed.

In light testing I almost always found the API correctly returns the 'move'
keypress (if there was one), I assume as the change event fires before the
key is released. I'm not sure it would if the key is released very fast or
if the Calculate event will take a long time to process your other code. But
even if it does that shouldn't matter, I think. If the calc event fires
before the change event, mbFlag will be false which signifies the change
event is about to follow. I'll leave that for you to test.

Anyway, for me it seems to be working reasonably well, but sadly 'reasonably
well' does not of course mean reliably in all scenarios. Whilst I've no
doubt it can be improved I'm not confident it can be made bullet-proof!

Regards,
Peter T

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!

OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -
[from first post]
Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.
This needs a lot of testing, I've haven't, but already I see problems that
needs sorting, eg:
- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag
- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to determine 'real'
changes.
- Undo ?
Thinking about it, even if the all above can be fixed I wouldn't be at all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !
' worksheet module code
Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer
Dim mbFlag As Boolean
' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell
Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Debug.Print "Calculate, Change event will follow = " & Not mbFlag
If mbFlag Then Debug.Print
mbFlag = False
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long
On Error GoTo errExit
mbFlag = False
If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then
ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If
For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if required
Exit For
End If
Next
mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub
'Undo' is setting the flag the 'wrong' way but I think can work around to
correct, but if code is doing stuff to clear the undo stack that won't be
required.
Regards,
Peter T

 

[Peter T]

I didn't think about F9, or Ctrl-Alt-F9

OK, with a manual calc the change event will not occur at all, either before
or after the Calc event.

Replace the calc event code with the following -

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim bF9 As Boolean
Dim bChangeEventNext As Boolean

bF9 = GetKeyState32(vbKeyF9) < 0

bChangeEventNext = (Not mbFlag And (Not bF9))

Debug.Print "Calculate, Change event will follow = " & bChangeEventNext

If mbFlag Then Debug.Print

mbFlag = False
End Sub

For me, the above in tandem with the other code now correctly differentiates
between the 3 cases you indicated, ie calc-change or change-calc, though
there still remain some scenarios that will falsely indicate a change event
is to follow the calc event.

You say you "only" need to differentiate between those three cases, but
surely you also need to distinguish data entry completed with the various
'move' keys, which the code aims to do.

Regards,
Peter T

Thanks for explaining! Here's what is not clear. You state:
"If the calc event fires before the change event, mbFlag will be false
which signifies the change
event is about to follow."
If I simply hit F9, then the sheetcalc event gets fired and mbFlag is
false, but no change event is going to follow since there was no data
entry.
At this point don't worry about all the different scenarios - I just
need to differentiate between the following 3 cases which cause a
calc:
1) F9
2) data entry is done followed by enter
3) data entry is done followed by a mouse clicking away

Just try the code as-is in the ThisWorkbook module of a new workbook.

Put the cursor in SetUpTest and run F5
Change cells in A1:A10 to trigger a calculation due to changing formula
result in B1

My debug results:

for most edit changes, including complete with Enter, or a press of any of
the 'move' keys gives debug pair-
Calculate, Change event will follow = True
Change, Calculate will follow = False

type new value, exit edit mode by clicking another cell gives debug pair-
Change, Calculate will follow = True
Calculate, Change event will follow = False

IOW seems predicate the order of the subsequent event as you asked.
But as I mentioned, the caveats for a few scenarios need to be sorted out.

I see you are handling the Windows API GetKeyState
in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed.

In light testing I almost always found the API correctly returns the 'move'
keypress (if there was one), I assume as the change event fires before the
key is released. I'm not sure it would if the key is released very fast or
if the Calculate event will take a long time to process your other code. But
even if it does that shouldn't matter, I think. If the calc event fires
before the change event, mbFlag will be false which signifies the change
event is about to follow. I'll leave that for you to test.

Anyway, for me it seems to be working reasonably well, but sadly 'reasonably
well' does not of course mean reliably in all scenarios. Whilst I've no
doubt it can be improved I'm not confident it can be made bullet-proof!

Regards,
Peter T

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!

OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -

[from first post]
Just for ideas, have a go with this in the Worksheet module (later almost
certainly will want to adapt to sheet module events). It's very important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future

calc
event.

In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.

This needs a lot of testing, I've haven't, but already I see

problems
that

needs sorting, eg:

- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag

- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a non-existent
calculate event ('cos the change event fired even though the value didn't
change). Could get messy if need to trap previous values to

determine
'real'

changes.

- Undo ?

Thinking about it, even if the all above can be fixed I wouldn't be

at
all

surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !

' worksheet module code

Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer

Dim mbFlag As Boolean

' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell

Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"

End Sub

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Debug.Print "Calculate, Change event will follow = " & Not mbFlag

If mbFlag Then Debug.Print
mbFlag = False
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long

On Error GoTo errExit
mbFlag = False

If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then

ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If

For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if required
Exit For
End If
Next

mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub

'Undo' is setting the flag the 'wrong' way but I think can work

around
to

correct, but if code is doing stuff to clear the undo stack that

won't
be

required.

Regards,
Peter T

 

[mwieder]

Peter - thank you very much for your help. I have implemented your
solution on our code (modified, but the basic idea is what you
suggested) which is actually a C# Add-In and it is working well. Of
course it may not be fool-proof, but I think it's the best we can
accompish to combat this bizzare Excel behavior.

I didn't think about F9, or Ctrl-Alt-F9

OK, with a manual calc the change event will not occur at all, either before
or after the Calc event.

Replace the calc event code with the following -

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim bF9 As Boolean
Dim bChangeEventNext As Boolean

bF9 = GetKeyState32(vbKeyF9) < 0

bChangeEventNext = (Not mbFlag And (Not bF9))

Debug.Print "Calculate, Change event will follow = " & bChangeEventNext

If mbFlag Then Debug.Print

mbFlag = False
End Sub

For me, the above in tandem with the other code now correctly differentiates
between the 3 cases you indicated, ie calc-change or change-calc, though
there still remain some scenarios that will falsely indicate a change event
is to follow the calc event.

You say you "only" need to differentiate between those three cases, but
surely you also need to distinguish data entry completed with the various
'move' keys, which the code aims to do.

Regards,
Peter T

Thanks for explaining! Here's what is not clear. You state:
"If the calc event fires before the change event, mbFlag will be false
which signifies the change
event is about to follow."
If I simply hit F9, then the sheetcalc event gets fired and mbFlag is
false, but no change event is going to follow since there was no data
entry.
At this point don't worry about all the different scenarios - I just
need to differentiate between the following 3 cases which cause a
calc:
1) F9
2) data entry is done followed by enter
3) data entry is done followed by a mouse clicking away

Just try the code as-is in the ThisWorkbook module of a new workbook.
Put the cursor in SetUpTest and run F5
Change cells in A1:A10 to trigger a calculation due to changing formula
result in B1
My debug results:
for most edit changes, including complete with Enter, or a press of any of
the 'move' keys gives debug pair-
Calculate, Change event will follow = True
Change, Calculate will follow = False
type new value, exit edit mode by clicking another cell gives debug pair-
Change, Calculate will follow = True
Calculate, Change event will follow = False
IOW seems predicate the order of the subsequent event as you asked.
But as I mentioned, the caveats for a few scenarios need to be sorted

out.

I see you are handling the Windows API GetKeyState
in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed.
In light testing I almost always found the API correctly returns the 'move'
keypress (if there was one), I assume as the change event fires before the
key is released. I'm not sure it would if the key is released very fast or
if the Calculate event will take a long time to process your other code. But
even if it does that shouldn't matter, I think. If the calc event fires
before the change event, mbFlag will be false which signifies the change
event is about to follow. I'll leave that for you to test.
Anyway, for me it seems to be working reasonably well, but sadly 'reasonably
well' does not of course mean reliably in all scenarios. Whilst I've no
doubt it can be improved I'm not confident it can be made bullet-proof!
Regards,
Peter T

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in the SheetChange event, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!
OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -
[from first post]
Just for ideas, have a go with this in the Worksheet module (later
almost
certainly will want to adapt to sheet module events). It's very
important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc
event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.
This needs a lot of testing, I've haven't, but already I see problems
that
needs sorting, eg:
- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag
- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a
non-existent
calculate event ('cos the change event fired even though the value
didn't
change). Could get messy if need to trap previous values to determine
'real'
changes.
- Undo ?
Thinking about it, even if the all above can be fixed I wouldn't be at
all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !
' worksheet module code
Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer
Dim mbFlag As Boolean
' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell
Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Debug.Print "Calculate, Change event will follow = " & Not mbFlag
If mbFlag Then Debug.Print
mbFlag = False
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long
On Error GoTo errExit
mbFlag = False
If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then
ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If
For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if
required
Exit For
End If
Next
mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub
'Undo' is setting the flag the 'wrong' way but I think can work around
to
correct, but if code is doing stuff to clear the undo stack that won't
be
required.
Regards,
Peter T

 

[mwieder]

Peter - thank you very much for your help. I have implemented your
solution on our code (modified, but the basic idea is what you
suggested) which is actually a C# Add-In and it is working well. Of
course it may not be fool-proof, but I think it's the best we can
accomplish to combat this bizzare Excel behavior.

I didn't think about F9, or Ctrl-Alt-F9

OK, with a manual calc the change event will not occur at all, either before
or after the Calc event.

Replace the calc event code with the following -

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim bF9 As Boolean
Dim bChangeEventNext As Boolean

bF9 = GetKeyState32(vbKeyF9) < 0

bChangeEventNext = (Not mbFlag And (Not bF9))

Debug.Print "Calculate, Change event will follow = " & bChangeEventNext

If mbFlag Then Debug.Print

mbFlag = False
End Sub

For me, the above in tandem with the other code now correctly differentiates
between the 3 cases you indicated, ie calc-change or change-calc, though
there still remain some scenarios that will falsely indicate a change event
is to follow the calc event.

You say you "only" need to differentiate between those three cases, but
surely you also need to distinguish data entry completed with the various
'move' keys, which the code aims to do.

Regards,
Peter T

Thanks for explaining! Here's what is not clear. You state:
"If the calc event fires before the change event, mbFlag will be false
which signifies the change
event is about to follow."
If I simply hit F9, then the sheetcalc event gets fired and mbFlag is
false, but no change event is going to follow since there was no data
entry.
At this point don't worry about all the different scenarios - I just
need to differentiate between the following 3 cases which cause a
calc:
1) F9
2) data entry is done followed by enter
3) data entry is done followed by a mouse clicking away

Just try the code as-is in the ThisWorkbook module of a new workbook.
Put the cursor in SetUpTest and run F5
Change cells in A1:A10 to trigger a calculation due to changing formula
result in B1
My debug results:
for most edit changes, including complete with Enter, or a press of any of
the 'move' keys gives debug pair-
Calculate, Change event will follow = True
Change, Calculate will follow = False
type new value, exit edit mode by clicking another cell gives debug pair-
Change, Calculate will follow = True
Calculate, Change event will follow = False
IOW seems predicate the order of the subsequent event as you asked.
But as I mentioned, the caveats for a few scenarios need to be sorted

out.

I see you are handling the Windows API GetKeyState
in theSheetChangeevent, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed.
In light testing I almost always found the API correctly returns the 'move'
keypress (if there was one), I assume as the change event fires before the
key is released. I'm not sure it would if the key is released very fast or
if the Calculate event will take a long time to process your other code. But
even if it does that shouldn't matter, I think. If the calc event fires
before the change event, mbFlag will be false which signifies the change
event is about to follow. I'll leave that for you to test.
Anyway, for me it seems to be working reasonably well, but sadly 'reasonably
well' does not of course mean reliably in all scenarios. Whilst I've no
doubt it can be improved I'm not confident it can be made bullet-proof!
Regards,
Peter T

Peter - thanks for working on sample code, but I'm not sure I
understand your solution. I see you are handling the Windows API
GetKeyState in theSheetChangeevent, but in my experience that event
fires after you have released the key and the KeyState would not
indicate any key as being pressed. Would you mind walking me through
your idea? I'll code it up once I understand it.
thanks!
OK, I've tried to recreate the code I forgot to post last time. As the
previously posted comments/caveats are important I'll re-post them -
[from first post]
Just for ideas, have a go with this in the Worksheet module (later
almost
certainly will want to adapt to sheet module events). It's very
important to
define the range of changing precedent cells that will trigger a calc,
otherwise the flag will be made a false positive for some future calc
event.
In a new wb run the setup routine, then edit/exit-edit A1:A10 in various
ways.
This needs a lot of testing, I've haven't, but already I see problems
that
needs sorting, eg:
- If values are pasted, the event order becomes Change > Calc, code as
written wrongly sets the flag
- edit a precedent cell but don't change its value, exit edit mode by
clicking another cell, will set the flag true in advance of a
non-existent
calculate event ('cos the change event fired even though the value
didn't
change). Could get messy if need to trap previous values to determine
'real'
changes.
- Undo ?
Thinking about it, even if the all above can be fixed I wouldn't be at
all
surprised if there are yet more problems to cater for, are you sure you
can't re-think your overall method !
' worksheet module code
Private Declare Function GetKeyState32 Lib "user32" _
Alias "GetKeyState" (ByVal vKey As Integer) As Integer
Dim mbFlag As Boolean
' in a new wb, run SetupTest, edit A1:10, complete entry with Enter
' or leave cell in edit mode with keys like arrow, PageDown etc
' and by clicking anbother cell
Sub SetUpTest()
Names.Add "Precedents", Range("A1:A10")
Range("B1").Formula = "=SUM(A1:A10)"
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Debug.Print "Calculate, Change event will follow = " & Not mbFlag
If mbFlag Then Debug.Print
mbFlag = False
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim bKeyPress As Boolean
Dim k As Long, i As Long
Dim ky(0 To 10) As Long
On Error GoTo errExit
mbFlag = False
If Not Intersect(Target, Range("Precedents")) Is Nothing Then
If Selection.Address <> Target.Address Then
ky(0) = GetKeyState32(vbKeyTab)
ky(1) = GetKeyState32(vbKeyLeft)
ky(2) = GetKeyState32(vbKeyRight)
ky(3) = GetKeyState32(vbKeyUp)
ky(4) = GetKeyState32(vbKeyDown)
ky(5) = GetKeyState32(vbKeyHome)
ky(7) = GetKeyState32(vbKeyEnd)
ky(8) = GetKeyState32(vbKeyPageUp)
ky(9) = GetKeyState32(vbKeyPageDown)
If Application.MoveAfterReturn Then
ky(10) = GetKeyState32(vbKeyReturn)
End If
For i = 0 To 10
If ky(i) < 0 Then
bKeyPress = True
k = i ' indicates the move key in the array if
required
Exit For
End If
Next
mbFlag = Not bKeyPress
End If
End If
errExit:
Debug.Print "Change, Calculate will follow = " & mbFlag
If Not mbFlag Then Debug.Print
End Sub
'Undo' is setting the flag the 'wrong' way but I think can work around
to
correct, but if code is doing stuff to clear the undo stack that won't
be
required.
Regards,
Peter T