Link a textbox in excel to a cell with more than 255 characters.

In excel I'd like to link a textbox to a cell that will populate the textbox with more than 255 characters.
Being unfamiliar with VBA, a search turned up a solution offered by Microsoft that I have edited to:

Sub Looper()
   Dim i As Integer
   Dim mytxt As String

    mytxt = WorksheetFunction.Rept("TableLegend3", 250)

   With Selection

      ' Initialize text in text box.
      .Text = ""
      For i = 0 To Int(Len(mytxt) / 255)
      .Characters(.Characters.Count + 1).Text = Mid(mytxt, (i * 255) + _
         1, 255)

   End With

End Sub

The cell that is intended to populate the textbox is named TableLegend3, is in a different worksheet and contains a formula that determines the text to appear in the text box.
The textbox is named Legend3TextBox and the VBA code is attached to the sheet with the textbox in it.

I've tried using a textbox from the insert menu and receive a Run-time error '1004': Application defined or object-defined error.
I've also tried using an ActiveX control textbox and receive a Run-time error '438': Object doesn't support this property or method.

Any suggestions to fix the existing code or provide a new code are greatly appreciated.
Thanks Dave
Who is Participating?
AL_XResearchConnect With a Mentor Commented:
Dave. Can you please accept my answer and close the question (tab like icon at the bottom-left of the browser) - otherwise EE will start sending you emails to complain you have not closed your questions off :)
Do you want to create this text box and define the link via VBA because if you don't then you can just insert a text box ActiveX control into a worksheet then set its property 'LinkedCell' to a cell reference (e.g. 'Sheet1!D4') and then you can indeed have a cell populate a text box with more than 255 characters and without any code at all.
DaveAuthor Commented:
Wow, was I ever making it complicated.
If there is nothing more complicated than that then go ahead and accept my answer :)

Happy to help !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.