Accessing Mutiple TextBoxes

[TimBro]

Hi again,

just googled around and discovered that the TextBoxes from the drawin
Toolbar kann handle up to 32,767 Character in Excel 97 ... how com
only 255 were saved to the array when I ran your code Peter ?

Regards
Ti

 

[Peter T]

For some odd reason, with strings over 255 in (drawing)
textboxes you need to read or write in chunks of up to
255, something like this:

Sub test3()
Dim str1 As String, str2 As String, shp As Shape
Dim j As Long, i as long

'manually create a textbox named "Text Box 3"
Set shp = ActiveSheet.Shapes("Text Box 3")

For j = 1 To 5
For i = 1 To 250
str1 = str1 & Chr(64 + j)
Next
str1 = str1 & vbLf & vbLf
Next
MsgBox Len(str1)
With shp
'write
j = 1

Do While j < Len(str1)
s = Mid(str1, j, 250)
.TextFrame.Characters(j).Insert String:=s
j = j + 250
Loop
MsgBox .TextFrame.Characters.Count

'read
j = 1
Do While j < .TextFrame.Characters.Count
str2 = str2 & .TextFrame.Characters(j, 250).Text
j = j + 250
MsgBox Len(str2)
Loop

End With
MsgBox Len(str2)
Debug.Print str2
End Sub

Regards,
Peter