I am having trouble sorting rows that contain linked textboxes. The textboxes move correctly, but they do not update the link location as they do when inserting or deleting rows; regardless of whether I use $ or not.

For example, suppose I start with this table:

........A.........B.........C
1...Number..Text.....Textbox
2......3..........a.......[white box linked to B2]="a"
3......2..........b.......[blue box linked to B3]="b"
4......1..........c.......[green box linked to B4]="c"

I've added unique color to each textbox to track where they go. Now I want to insert a row with '4', 'd', and a red textbox at the top (under the title in row 2). When I do so I get the following table:


........A.........B.........C
1...Number..Text.....Textbox
2......4..........d.......[Red box linked to B2]="d"
3......3..........a.......[white box linked to B3]="a"
4......2..........b.......[blue box linked to B4]="b"
5......1..........c.......[green box linked to B5]="c"

Note that after inserting the row, all of the links for the textboxes automatically update to continue referencing their own row, as expected. For example row 2 with '3', 'a', and a white box linked to B2="a" is now at row 3 and still has '3', 'a', and a white box now linked to B3="a".

However, if I sort the rows by the 'text' column (B) in ascending order I get the following:


........A.........B.........C
1...Number..Text.....Textbox
2......3..........a.......[white box linked to B3]="b"
3......2..........b.......[blue box linked to B4]="c"
4......1..........c.......[green box linked to B5]="d"
5......4..........d.......[Red box linked to B2]="a"

All of the text boxes moved with their rows appropriately. However, they continue to link to their original location. For example, before the sort row 2 had '4', 'd', and a red box linked to B2="d", after the sort the row moved to row 5 which now has '4', 'd', and a red box linked to B2="a". So, unlike inserting or deleting rows where excel maintains localized linking, sorting leaves it alone, destroying the local linking.

Because of this behavior I am forced in to one of 3 options and don't know which is best, nor how to do it.

1. Move the row numbers with the rows so that the hard-coded links continue to work. I don't know if this can be done, nor how.

2. Dynamically modify the link locations when the text box moves to stay linked to the correct cell. This is already done automatically when inserting or deleting rows. But again, I don't know how to do this.

3. Just bag the linking. Obviously my real use case is more complex and there is a reason for linking the textbox to a cell in the same row. But if it can't be done I'll just have to remove the link and type the data straight in to the textbox itself.

So if someone can help me with solution 1 or 2, I would greatly appreciate it. Otherwise I'll just have to live with solution 3.

Thanks for your time!