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

Question

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

Get FORMATTED text from a Textbox, and manipulate it (like HTML perhaps). And
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.

Results are in the comments in the code.

Ross
– Ross

2014-02-13 21:24:27 +00:00
Commented Feb 13, 2014 at 21:24 — Ross
– Ross, Commented Feb 13, 2014 at 21:24

Tim Williams · Accepted Answer · 2014-02-13 22:17:05Z

6

Sub Tester()
    CopyText ActiveSheet.Shapes("txtOne").TextFrame, _
             ActiveSheet.Shapes("txtTwo").TextFrame
End Sub



Sub CopyText(tf1 As TextFrame, tf2 As TextFrame)

    Dim n, f As Font

    tf2.Characters.Text = tf1.Characters.Text

    For n = 1 To tf1.Characters.Count
        Set f = tf1.Characters(n, 1).Font
        With tf2.Characters(n, 1).Font
            .Bold = f.Bold
            .Color = f.Color
            .Italic = f.Italic
            'add other properties as needed...
        End With
    Next n
End Sub

edited Feb 13, 2014 at 22:17

answered Feb 13, 2014 at 21:37

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2014-02-13 21:13:34Z

0

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

answered Feb 13, 2014 at 21:13

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

Ross Over a year ago

This actually led me to find out that there's a bunch of options (or things to copy) from the Font object. Thanks. msdn.microsoft.com/en-us/library/office/ff840959.aspx

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related