Cell content change Event

[faffo1980]

HI all,
I would like to know if there is an event bringing the information of ALL
cells that have changed.
For example: cell ("A1") has value 5.
Cell("A2") has value =A1*2.
If I change the A1 content the event SheetChange brings the information that
A1 has changed. How can I discover that also A2 value has changed?
Is accessing to Dependent cells the only way?

Thanks

faffo1980

 

[Patrick Molloy]

unfortunately, changes due to calcs don't fire events
an alternative would be to keep another sheet with the main sheet's values
.... then you could pick up whats changed.

 

[Rick Rothstein]

Describe what you mean by "discover that also A2 value has changed"... there
may be code to do what you want depending on what you mean by this. Tell us
what you want to happen and what you want to do when it happens.

 

[faffo1980]

Hi,
I'm sending data of the calculated cell on a socket. So I would like sending
updates as soon as the value of cell changes (in this case due to a value of
another cell)

Faffo1980

 

[Rick Rothstein]

You might be able to make use this Change event structure to do what you
want...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, DependentCells As Range, ChangedCells As Range
Set ChangedCells = Target
On Error Resume Next
For Each R In Target
Set DependentCells = R.Dependents
If Not DependentCells Is Nothing Then
Set ChangedCells = Union(ChangedCells, R.Dependents)
End If
Next
For Each R In ChangedCells
Debug.Print R.Value
Next
End Sub

When you reach the last For..Each loop, the ChangedCells range variable will
contain a reference to each cell that has changed as a result of the
physical change you made to one (or more) cells. So, you can reference any
cell property you need to as you iterate the loop. In my example, I simply
print the changed cells value to the Immediate window. Note... I have not
restricted the actions of this event to any particular range, so **any**
change you make (even deletions) any where on the sheet, whether that cell
has dependents or not, will return at least one reference in ChangedCells
(which would be the actual cell you changed).

 

[faffo1980]

Thanks a lot Rick,
I fould your code very useful.

Regards

Fabrizio

 

[faffo1980]

Hi,
I have another question,
Do Dependents range includes also range on different worksheets?

Tahnks

Faffo1980

 

[Rick Rothstein]

No... unfortunately the Dependents and Precedents properties only work on
the worksheet that the target cell is on.