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.

On Oct 12, 11:03 am, "Peter T" <peter_t@discussions wrote:
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

wrote in message

oups.com...

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

On Oct 11, 7:35 pm, "Peter T" <peter_t@discussions wrote:
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

wrote in message

roups.com...

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!

On Oct 11, 4:30 pm, "Peter T" <peter_t@discussions wrote:
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