How Do I Automatically Detect Cell Changes In Excel

  • Thread starter Thread starter Gordon
  • Start date Start date
G

Gordon

Hi there,

I have a VB.NET application which opens a new Excel workbook (and obviously
worksheet). The cell values in the worksheet depend on certain formulae,
therefore the sheet Calculate event is triggered whenever a calculation
occurs.

My problem is that I can't find a way within this event to find out exactly
which cell has changed. This is possible with the sheet Change event,
because it passes back the changed row as a parameter, but unfortunately
this event event doesn't get triggered when a calculation occurs, so I can't
use it.

Are there maybe any Excel.Range events that I can use to resolve this issue
?

Help !

Gordon.
 
There is no way to get or event that reveals the cell or action that
triggered the calculate (the user could have done it by hitting F9 for
example).
 
Hi Gordon,

As Tom pointed out, there is no built-in way to determine this. The only
thing I can think of would be as follows:

1) When the calculation event fires, check the worksheet's values against
the hidden worksheet (see step 2) if it exists.

2) Now, check to see if your hidden sheet exists - if not, create it. Copy
/ paste values from the "calculation" sheet to the hidden sheet.

This would be pretty slow, especially for large worksheets (you'd have to
iterate through the cells or an array to compare the worksheets). But it's
the only way I can think of to do what you're looking for. And if your
calculations take a long time anyway, the user might not notice the extra
time.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Sorry if I missed something, but what are those formulae dependent on? Is
it other cells that would trigger the sheet_change event? If so, could you
find those cells (through the sheet_change target range) and trace their
dependents through to the cells you care about, or vice versa, monitor the
precedent cells to the ones you care to monitor?
 
Back
Top