Hi,

I have a text box, "TextBox 1" referencing a cell named "Companies". There is over 255 characters in cell "Companies", hence the need for the VBA code below.

My problem is, the text box does not change and only contains 255 characters, I cannot see the error in the code below.

Your advice will be much appreciated.
K 


Private Sub Worksheet_Change(ByVal Target As Range)
  
      ' Dimension the variables.
      Dim txtBox1 As TextBox
      Dim theRange As Range, cell As Range
      Dim startPos As Integer

      ' Set txtBox1 equal to the active sheet's TextBox object. You can
      ' replace the ordinal number with your text box name in quotes.
      ' For example: ActiveSheet.DrawingObjects("Text 1")
      Set txtBox1 = ThisWorkbook.Sheets("BS0").DrawingObjects("TextBox 1")

      ' Set a range on the active sheet equal to the range object text
      ' that you are interested in copying to the text box.
      Set theRange = Worksheets("Company").Range("Companies")

      'Set the starting position for the text.
      startPos = 1

      ' Create a For-Each construct to loop through the cells in the range.
      For Each cell In theRange

         ' Populate the textbox with the cell values using the Characters
         ' method.
         ' Note: Chr(10) can be used to add a new line in the textbox for
         ' each cell.
         txtBox1.Characters(Start:=startPos, _
            Length:=Len(cell.Value)).Text = cell.Value & Chr(10)

         ' Update the startPos variable to keep track of where the next
         ' string of text will begin in the textbox.
         startPos = startPos + Len(cell.Value) + 1

      Next cell
   End Sub