Solved

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

Posted on 2016-08-24
5
145 Views
Last Modified: 2016-08-30
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
Comment
Question by:Dave
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41769495
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
 

Author Comment

by:Dave
ID: 41769558
Thanks!
Wow, was I ever making it complicated.
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41769568
If there is nothing more complicated than that then go ahead and accept my answer :)

Happy to help !
0
 
LVL 3

Accepted Solution

by:
AL_XResearch earned 500 total points
ID: 41776740
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
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41776978
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question