Appropriate Object To Hold Memo-Sized Text?

[PeteCresswell]

Looks like .Shapes.AddTextBox creates an object that will only hold
up to 255 chars.

e.g. .Shapes("txtWhatever").TextFrame.Characters.Text = string(255,
"A") works, but
..Shapes("txtWhatever").TextFrame.Characters.Text = string(256, "A")
just quietly fails to add the text without trapping out.

Is there a more appropriate object to use when loading larger amounts
of text? 32,760 would probably suffice.

Or should I just define a range that points to a monster cell?

 

[Peter T]

You need to add characters in chunks of 255 or less into a textbox, eg -

Sub test()
Dim shp As Shape
Dim sText As String, sPart As String

For i = 65 To 84
For j = 1 To 1000
sText = sText & Chr(i)
Next
sText = sText & vbLf
Debug.Print Len(sText)
Next

'MsgBox Len(sText)

Set shp = ActiveSheet.Shapes.AddTextbox(1, 10, 10, 600, 3000)

With shp
j = 1
Do While j < Len(sText)
sPart = Mid$(sText, j, 250)
.TextFrame.Characters(j).Insert String:=sPart
j = j + 250
Loop
End With

End Sub


With a very long strings, 15-20k or more, the textbox does not always
receive all characters, though without error and no particular defined
limit. Seems to partly depend on the initial size of the textbox. For your
32k it might be worth looking at an appropriate 'embedded' app to hold the
text, eg Word or Notepad.

Regards,
Peter T

 

[(PeteCresswell)]

Per Peter T:

With a very long strings, 15-20k or more, the textbox does not always
receive all characters, though without error and no particular defined
limit. Seems to partly depend on the initial size of the textbox. For your
32k it might be worth looking at an appropriate 'embedded' app to hold the
text, eg Word or Notepad.

Short of a proper embedded application object, what do you think
about just pushing it into a .Range made up of many merged cells
- which is what I'm doing now, just to get something to show the
client?

Only reason I got off on the TextBox tangent was the appeal of
object names whose scope was limited to a worksheet. e.g. If I
have 25 funds - each with their own worksheet - I can push the
text into "txtComment" on each sheet, rather than having to
custom-name a range for each sheet.

 

[Peter T]

Yes, a cell can store and display 32K, depending on layout perhaps it might
be easier in some merged cells.

rather than having to
custom-name a range for each sheet.

Not quite sure what the problem is with that but FWIW, if you define at
worksheet level, each sheet can contain the same Name which may or may not
refer to same cell address on each sheet.

Regards,
Peter T