Sub Test()

Dim Txt As String
'get some text to play with from a hidden textbox
Txt = GetTextBoxText(ActiveSheet.TextBoxes("txtBox2"))

'***** this is how you set text ********
SetTextBoxText ActiveSheet.TextBoxes("txtBox"), Txt

MsgBox "The length of the text is " & Len(GetTextBoxText(ActiveSheet.TextBoxes("txtBox")))
    
End Sub

'sets long text>255 chars
Sub SetTextBoxText(TB As TextBox, S As String)

Dim i As Long
With TB
      ' Initialize text in text box
      .Text = ""
      For i = 0 To Int(Len(S) / 255)
      .Characters(.Characters.Count + 1).Text = Mid(S, (i * 255) + _
         1, 255)
     Next
End With

End Sub

'Recovers text>255 chars from a textbox
Function GetTextBoxText(TB As TextBox) As String 'xxx

Dim xTmp As String, xTmp1 As String, tStart As Integer, xMax As Integer
Dim tStep As Integer

tStart = 1
xMax = TB.Characters.Count + 1
tStep = 255

Do
 If tStart > xMax Then Exit Do
 If tStart + tStep > xMax Then tStep = xMax - tStart + 1
 xTmp1 = TB.Characters(tStart, tStep).Text
 xTmp = xTmp & xTmp1
 tStart = tStart + 255
Loop

GetTextBoxText = xTmp

End Function