• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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

Hello
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)

   ActiveSheet.Shapes("Legend3TextBox").Select
     
   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)
      Next

   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
0
Dave
Asked:
Dave
  • 4
1 Solution
 
AL_XResearchCommented:
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.
0
 
DaveAuthor Commented:
Thanks!
Wow, was I ever making it complicated.
0
 
AL_XResearchCommented:
If there is nothing more complicated than that then go ahead and accept my answer :)

Happy to help !
0
 
AL_XResearchCommented:
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 :)
0
 
AL_XResearchCommented:
Thanks
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now