Solved

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

Posted on 2016-08-24
5
160 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

Independent Software Vendors: 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

Title # Comments Views Activity
look up for 2 numbers of 3 2 34
need count numbers IN ranges 25 29
drag and drop a row in excel 2010 5 41
Stop macro from going to a different sheet in Excel 18 47
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

738 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