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.

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

wrote in message

ups.com...

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!