Looking for a way to track and reference cells in VBA

[ScottGall]

Good Day,

I am searching for a way to track cells so that I know where they have been moved to if a user editing the workbook adds columns/rows, cuts / pastes cells.

I have found that I can use a Range variable in a module to keep track of movements of cells within a sheet, but if those cells are cut and pasted onto another sheet I find that the fact that it has moved sheets is lost.

Consider the following VBA in a module:

Dim MyRange As Range

Public Sub GetReference()
    Set MyRange = Sheet1.Range("A1")
End Sub

Public Sub PrintReference()
    Debug.Print MyRange.Parent.Name & "->" & MyRange.Address & "->"; MyRange.Value2
End Sub

If we put a string lets say "testing" in A1 if sheet1 then:

1. Run GetReference
2. Run PrintReference
   you will get "Sheet1->$A$1->Testing" printed in the immediate window
3. Cut Sheet1!A1 and paste it on Sheet3!B2
4. Run PrintReference
   you will get "Sheet1->$B$5->"

It seems that the address tracking part still works, yet the Range.Parent does not get updated when the paste occurs, and the Value2 parameter is somehow is reinitialized as another instance.

My questions are how can I identify:

1. That the user cut and pasted the reference to another sheet
2. what that new sheet is

Any other points would be helpful in understanding this automation challenge.

Thank you.

[#2]

@ScottGall 

The point is you set the variable MyRange in the code GetReference.

And in the code PrintReference, you are printing it's properties only without setting it again and since you declared the MyRange variable is the module level variable, it is still have a reference set in GetReference code. And you are not getting it's value in the Immediate Window because you cut that cell and pasted at another location and the range MyRange which was previously set to refer to the Sheet1!A1 is still referring that cell which is now empty and therefore you are not getting its' value.

If you have one such case where you want to track a cell and all it's properties even if it is cut and paste into another location, you may do something like this...

1. Select the cell A1 on Sheet1.
2. Type MyRange in the Name Box to create a Named Range which refers to the cell A1 on Sheet1.
3. Enter any value in A1 on Sheet1, say 'Testing'.
4. Now run the code PrintReference given below.
5. Now cut the cell and paste it into any available sheet in the file.
6. Run the code PrintReference again and you will get it's latest properties.

Dim MyRange As Range
Public Sub PrintReference()
    Set MyRange = Range("MyRange")
    Debug.Print MyRange.Parent.Name & "->" & MyRange.Address & "->"; MyRange.Value2
End Sub

[#3]

@Subodh_Tiwari_sktneer 

Thank you for your response.

The named range approach you suggest could work for needing to track small numbers of cells <30K, the reason I was interested in using a reference in VBA was to make the tracking lighter (ultimately I'll be using C#) however the object model is shared so I hoped to learn something here. Using named ranges to track the number of cells (~600K) I need would have performance impacts on the workbooks, not to mention with Named Ranges you will run into memory problems after about 64K.

What struck me with the VBA approach was that as long as I am cutting and pasting on the same sheet it works as I expect:

MyRange.Parent.Name = "Sheet1"

MyRange.Address = "$B$5" (or whatever address you paste to)

MyRange.Value2 = "Testing" (or whatever you type in the cell before or after moving it)

however if I paste on another sheet I get:

MyRange.Parent.Name = "Sheet1" (no mater what sheet you pasted on

MyRange.Address = "$B$5" (or whatever address you paste to)

MyRange.Value2 = Empty

It's almost like the object is reinitialized then some properties are cloned from the previous object.

Even if I could identify the ranges where this occurred in a trustworthy way I could deal with those cases, the problem I have is that the MyRange gives no indication of failure, just ambiguous wrong information.

Any additional thoughts would be most appreciated.