Excel 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
SheetSelectionChange 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
SelectionChange 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
SelectionChange event that is about to be called? Is there some other
suggestion as to how we can handle this case?
thanks!

 

[Bill Renaud]

Interesting! I use Excel 2000, and I always see the "Calculate" event
first, followed by the "SelectionChange" event, regardless of whether I hit
"Enter" or whether I select a different cell to enter the changed value
into the previously edited cell.

Would using only the "Change" event work for you, rather than using both
the "Calculate" and "SelectionChange" events? It seems that this would be
what you are really trying to capture. On my machine, the "Calculate" event
occurs before the "Change" event, so your worksheet should be up-to-date
before you attempt to write back to storage.

You might be able to use Application.Calculate in your "Change" event
handler to make sure the worksheet has been calculated anyway.