Sort Rows With Linked Textboxes

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.

This line

If Not Intersect(Range(col & ":" & col), TB.TopLeftCell) Is Nothing Then

simplifies to

If TB.TopLeftCell.Column = col Then

Originally Posted by shg

This line

If Not Intersect(Range(col & ":" & col), TB.TopLeftCell) Is Nothing Then

simplifies to

If TB.TopLeftCell.Column = col Then

Good point. Thanks.