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

[sbo]

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?

 

[JLGWhiz]

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]

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]

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]

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!