Create a text box for each row of data and fill that textbox with concatenated cell value.

Hi all,

I have n number of rows in a table and n number of columns.

For each row I would like to create a unique textbox and the values of everything in that row being populated into that textbox.
I figured I could concatenate the row and get the information for the textbox from there.

Many thanks

Andy

That works perfectly, thank you Bishonen.

Hi,

I was wondering if the code above could be modified to create a dynamic link between the cells and the textboxes?

The code above creates a text box for each row in my table and populates that text box with values in a specific column.

Here are my changes so far:

Sub TextBoxLine2()

Dim Cell As Range, Row As Range
Dim sText As String
Dim iCounter As Long
Dim i As Long
Dim tagRow As Long
Dim tagTF As Long
Dim iWidth As Long

i = 2
tagRow = 2

For Each Row In Range("C2:C1000").Rows
For Each Cell In Row.Cells
  iWidth = Cells(i, 6)
  tagTF = Cells(tagRow, 4)
  
        If sText = "" Then
            sText = Cell.Value
        Else
            sText = sText & " " & Cell.Value
        End If

    Next Cell
  
    ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 811, iCounter, iWidth, 75).Select

    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = sText
    
    With Selection
    .Font.Size = 8
    End With
    
    iCounter = iCounter + 75
    sText = ""
    
        If tagTF = 0 Then
        With Selection
        .Font.Color = vbWhite
        End With
        With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
        .Transparency = 0
        .Solid
        End With
   End If
    i = i + 1
    tagRow = tagRow + 1

Next Row

End Sub

As you can see I get the cell info from column C. For each row a unique textbox is created for that value from C.

Trouble is once the box has been created the only way to change the value inside is to manually edit it. As you can see there may be hundreds of text boxes and although I could simply run the routine again and create new boxes with the updated info I still need to locate the original box and delete it.

I's simply like to be able to make the changes in the table and have the corresponding textbox update automatically.

Any ideas?

thanks

Andy