Interesting problem with VBA generated textbox (or textframe of shape) length

[Bill Dilworth]

Here is some basic vba code for automating an Excel sheet from a PPT app. I
want to add a rectangle with some text to sheet 1. I know (because I
control the file creation) that this will be the 6th shape on sheet 1. So I
used this code:


oExcel.Sheets(1).Shapes _
.AddShape(1, y + 10, (2 * x) + 20, y, x) _
.TextFrame.Characters.Text = _
strText

Works great, up to a point. If strText gets to about 200-250 characters, it
blanks out the text display in the box, nada, nothing, gone, pfft. I tried
changing the shape to a textbox (AddTextBox), with no change in behavior.
Since I know the text will be between 10 and 400 characters, depending on
the circumstances, I am forced to run with something like this:


oExcel.Sheets(1).Shapes _
.AddShape(1, y + 10, (2 * x) + 20, y, x) _
.TextFrame.Characters.Text = _
Left(strText, 200)

If Len(strText) > 200 Then _
oExcel.Sheets(1).Shapes(6) _
.TextFrame.Characters(200) _
.Insert Right(strText, _
Len(strText) - 200)


I just wondering if I am missing something silly, or is this a standard
Excel design "feature"? Perhaps this is a cross app VBA inconvenience, but
it kept me up half the night, banging my head against the monitor, trying to
figure out where strText went half the time. The code now works, so this
isn't a crisis anymore, but I would love to find out why it does this.

 

[NickHK]

Bill,
No solution, but I see it happens when Len becomes bigger than 255. Excel is
unable to display text in cells formatted as Text at this length also, so I
would guess it has something to do with how Excel handles strings.

NickHK

 

[Peter T]

Hi Bill,

If your string is over 255 need to insert characters into the textframe in
chunks of
less than 255 at a time.


Sub test()
Dim j As Long
Dim sPart$, sText$
Dim shp As Shape

sPart = Application.Rept("x", 37) & "40 "
For j = 1 To 25
sText = sText & sPart
Next
MsgBox Len(sText)
Set shp = ActiveSheet.Shapes.AddTextbox(1, 99#, 9#, 450#, 210#)

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

End Sub

I'd be interested to know how much text others can put in a textbox. I
appear to get inconsistent results, at first limited to 1024 then later, for
no apparent reason, significantly more than that. Increase the loop "For j =
1 To 25" and the textbox size to accommodate.

Regards,
Peter T

 

[Kate]

Bill, thank you for posting this problem. I had just
encountered bizarre behavior in a textbox,
similarly populated from VBA. I was displaying data
validation messages. It worked fine for
all but one column of cells. I could see that the lenght of
the title was being used, the code stepped
through correctly, but the validation message of the
previously-entered cell was being displayed instead!! You
have solved my problem, and a LOT of potential tears that
were just getting ready to flow!!

Kate