How to get notified when user inserts cells, rows, or columns in W
 

I'm making a client to Excel that keeps references to cells on worksheets.
These references must be updated if the user inserts cells to the left or
above the referenced cells. I have not been able to find this information,
neither in the Range that accompanies the Change notification nor in any of
the Worksheet, Workbook, or Application properties. Can anybody help me?

How to get notified when user inserts cells, rows, or columns in W
 

Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:

If Target < (Reference Cell) Then
If Target.Row <=( Reference Cell).Row And _
Target.Column <= (Reference Cell).Column Then
'Update code here
End If
End If

The outer If statement avoids inadvertant changes to the reference cell.
The inner statement then checks to see if the target cell that is changed is
to the left or above the reference cell.


"sbo" wrote in message
...
I'm making a client to Excel that keeps references to cells on worksheets.
These references must be updated if the user inserts cells to the left or
above the referenced cells. I have not been able to find this information,
neither in the Range that accompanies the Change notification nor in any
of
the Worksheet, Workbook, or Application properties. Can anybody help me?

How to get notified when user inserts cells, rows, or columns
 

The problem is that the Change event is emitted in many situations, both when
deleting and inserting cells and when changing cell contents. Furthermore,
the insertion (or deletion) may push cells down (up) or right (left). So more
accurate information is needed. I also thought of intercepting the user
action that initiated the change but it seems hopeless to catch all scenarios.

"JLGWhiz" wrote:


Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:

If Target < (Reference Cell) Then
If Target.Row <=( Reference Cell).Row And _
Target.Column <= (Reference Cell).Column Then
'Update code here
End If
End If

The outer If statement avoids inadvertant changes to the reference cell.
The inner statement then checks to see if the target cell that is changed is
to the left or above the reference cell.


"sbo" wrote in message
...
I'm making a client to Excel that keeps references to cells on worksheets.
These references must be updated if the user inserts cells to the left or
above the referenced cells. I have not been able to find this information,
neither in the Range that accompanies the Change notification nor in any
of
the Worksheet, Workbook, or Application properties. Can anybody help me?


.

How to get notified when user inserts cells, rows, or columns
 

I mis-read the insert cells bit. Have you tried using named ranges to
overcome the reference changes caused by the user inserting cells, rows or
columns? The named range, although moved to a different relative location
on the sheet will keep the name and can be referred to by name in formulas
and code.



"sbo" wrote in message
...
The problem is that the Change event is emitted in many situations, both
when
deleting and inserting cells and when changing cell contents. Furthermore,
the insertion (or deletion) may push cells down (up) or right (left). So
more
accurate information is needed. I also thought of intercepting the user
action that initiated the change but it seems hopeless to catch all
scenarios.

"JLGWhiz" wrote:


Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:

If Target < (Reference Cell) Then
If Target.Row <=( Reference Cell).Row And _
Target.Column <= (Reference Cell).Column Then
'Update code here
End If
End If

The outer If statement avoids inadvertant changes to the reference cell.
The inner statement then checks to see if the target cell that is changed
is
to the left or above the reference cell.


"sbo" wrote in message
...
I'm making a client to Excel that keeps references to cells on
worksheets.
These references must be updated if the user inserts cells to the left
or
above the referenced cells. I have not been able to find this
information,
neither in the Range that accompanies the Change notification nor in
any
of
the Worksheet, Workbook, or Application properties. Can anybody help
me?


.

How to get notified when user inserts cells, rows, or columns
 

Thank you for this suggestion, which I think is very good. I my scenario I'm
a bit worried about how Excel would handle thousands of named ranges, but I
should give it a try since there apparently is no other solution. So: thank
you very much for your help!

"JLGWhiz" wrote:

I mis-read the insert cells bit. Have you tried using named ranges to
overcome the reference changes caused by the user inserting cells, rows or
columns? The named range, although moved to a different relative location
on the sheet will keep the name and can be referred to by name in formulas
and code.



"sbo" wrote in message
...
The problem is that the Change event is emitted in many situations, both
when
deleting and inserting cells and when changing cell contents. Furthermore,
the insertion (or deletion) may push cells down (up) or right (left). So
more
accurate information is needed. I also thought of intercepting the user
action that initiated the change but it seems hopeless to catch all
scenarios.

"JLGWhiz" wrote:


Using the Worksheet_Change event, you could probably evaluate the row and
column values of the target cell against the criteria cell:

If Target < (Reference Cell) Then
If Target.Row <=( Reference Cell).Row And _
Target.Column <= (Reference Cell).Column Then
'Update code here
End If
End If

The outer If statement avoids inadvertant changes to the reference cell.
The inner statement then checks to see if the target cell that is changed
is
to the left or above the reference cell.


"sbo" wrote in message
...
I'm making a client to Excel that keeps references to cells on
worksheets.
These references must be updated if the user inserts cells to the left
or
above the referenced cells. I have not been able to find this
information,
neither in the Range that accompanies the Change notification nor in
any
of
the Worksheet, Workbook, or Application properties. Can anybody help
me?


.



.