Cell content change Event
 

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).

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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

Cell content change Event
 

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

Regards

Fabrizio

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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

Cell content change Event
 

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

Tahnks

Faffo1980

"faffo1980" wrote:

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

Regards

Fabrizio

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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

Cell content change Event
 

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

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
Hi,
I have another question,
Do Dependents range includes also range on different worksheets?

Tahnks

Faffo1980

"faffo1980" wrote:

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

Regards

Fabrizio

"Rick Rothstein" wrote:

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).

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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" wrote:

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.

--
Rick (MVP - Excel)


"faffo1980" wrote in message
...
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