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
Solved

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

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

837 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