I couldn't get it to work in VBA either. I changed it to iteratively
read off the left 255 characters then delete then, and that approach
worked (see code below). However, when I tried to put the characters
back into the text box at the end of the procedure, for some reason it
didn't work.

You could try using the following algorithm on a copy of the text box,
then simply delete the copy at the end of the method.

Sub LongTextBoxVBA()

Dim ws As Worksheet: Set ws = ActiveSheet
Dim tb As Shape
Dim s As String
Dim numChars As Integer

Set tb = ws.Shapes("Text Box 1")
s = ""
numChars = 255
If tb.TextFrame.Characters.Count < 255 Then numChars =
tb.TextFrame.Characters.Count
Do While tb.TextFrame.Characters.Count 0
s = s & tb.TextFrame.Characters(1, numChars).Text
tb.TextFrame.Characters(1, numChars).Delete
If tb.TextFrame.Characters.Count < 255 Then numChars =
tb.TextFrame.Characters.Count
Loop
ws.Range("B2").Value = s
tb.TextFrame.Characters.Text = s

Set tb = Nothing
Set ws = Nothing

End Sub


-- Nick Hebb
http://www.breezetree.com