Textbox text (Shape)

[Krishnakumar]

Hi All,

How can I store the characters of a textbox (Shape) in a variable?

Any help would be appreciated.

 

[VoG]

Try this

Sub test()
Dim mytext As String
mytext = Sheets("Sheet1").TextBoxes("Text Box 1").Text
MsgBox mytext
End Sub

 

[Krishnakumar]

Hi, Vog,

Thanks for the reply. But the problem is it only returns 255 characters.

How can I store all the characters?

 

[RoryA]

You have to loop unfortunately:

   Dim mytext As String
   Dim tb As TextBox
   Dim lngIndex As Long
     Set tb = ActiveSheet.TextBoxes("Text Box 1")
    With tb
        For lngIndex = 1 To .Characters.Count Step 255
            mytext = mytext & .Characters(lngIndex, 255).Text
        Next
    End With
    MsgBox mytext

 

[Krishnakumar]

Hi Rory,

Thanks. It works fine.

 

[ashleedawg]

A decade late but figured I'd pass on my solution as well, for VBA Word Wrap.

Defaults to width of 115 but you can optionally specify whatever. Split with CR + LF.

    Public Function wrapText(strIn As String, Optional maxLen As Long = 110) As String
        Dim p As Long: wrapText = strIn
        Do
            p = InStrRev(wrapText, " ", p + maxLen) - 1
            wrapText = Left(wrapText, p) & vbCrLf & Right(wrapText, Len(wrapText) - p - 1)
        Loop While p + maxLen < Len(wrapText)
    End Function