Sort Rows With Linked Textboxes

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!

Why do you need text boxes?

Originally Posted by shg

Why do you need text boxes?

To allow a unified row size regardless of the amount of text in a specific description. Because cells cannot embed a scroll bar, the text boxes allow me to display the information using scroll bars. Linking it to a cell allows for easier input.

Then end result is our display is nicer. If there is no work-a-round I will either remove the text boxes and turn off Autofit for the row height or remove the link to a cell and require entering data straight in to the text box.

Nevertheless, if there is a work-a-round I would like to discover it. Not only for my current use but because I'd now like to know exactly what the limitation of this usage is, just for the sake of knowing.

Muchas Gracias!

That's a well-considered reason. Alas, I have no suggestion, but if you find a workable one elsewhere, drop back and let us know.

Originally Posted by shg

That's a well-considered reason. Alas, I have no suggestion, but if you find a workable one elsewhere, drop back and let us know.

Here is my solution based on feedback I found:

' col is the column the boxes are in, colOffset is the offset from where the text is
Sub UpdateSortedBoxes(col As String, colOffset As Integer)

    Dim TB As OLEObject

    For Each TB In Application.ActiveSheet.OLEObjects
        If TB.Name Like "TextBox*" Then
            If Not Intersect(Range(col & ":" & col), TB.TopLeftCell) Is Nothing Then
                TB.LinkedCell = TB.TopLeftCell.Offset(0, colOffset).Address
            End If
        End If
    Next
   
End Sub

Well done, thanks for posting back.