Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Cell Content | Excel Discussion (Misc queries) | |||
Cell Background Color Change -- Change Event | Excel Programming | |||
apply cell change event to single column - WorksheetChange Event | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |