Copying/Manipulating Formatted Text in Textboxes in Excel 2013 using VBA

Question

I am trying to do two things in Excel 2013 using VBA:

1. Get FORMATTED text from a Textbox, and manipulate it (like HTML perhaps). And
2. Copy FORMATTED text from a Textbox, to another Textbox.

I have tried two things:

'Copies text only. No formatting, but with proper line breaks
Dim txtContent As String
txtContent = Worksheets("TextBox").Shapes("TextBox1").TextFrame.Characters.Text
Worksheets("TextBox").Shapes("TextBox 3").TextFrame.Characters.Text = txtContent

The second approach was similar to above:

'Does not do anything. Produces Run-time error 91
Dim myFrame As TextFrame
myFrame = Worksheets("TextBox").Shapes("TextBox1").TextFrame
Worksheets("TextBox").Shapes("TextBox 3").TextFrame = myFrame

Please help.

Answer 1

You can either set or retrieve the formatting of text characters in a TextBox as follows:

Sub durall()
    ActiveSheet.Shapes("TextBox 1").TextFrame.Characters.Font.ColorIndex = 3
End Sub