[VBA] How to populate a textbox with formatted text?

[MrD101]

I have text string in a cell on tab A. For example, let's say it is: "The red dog was very big."

I would like to populate a textbox on tab B with the string including the formatting of the string (the bold and color included). In my case a person would select a cell and click a button to populate the textbox on the other tab.

If I try to do something like this:

Sheets("B").Shapes("TextBox 1").TextFrame.Characters.Text = ActiveCell.Value

I get the text but lose the formatting. I also tried ActiveCell.Text with no luck.

How can I accomplish this?

 

[Rick Rothstein]

UserForm TextBoxes cannot do that... all the text in them must be the same format.

 

[MrD101]

UserForm TextBoxes cannot do that... all the text in them must be the same format.

I was afraid that might be the answer. Are they any other options instead of a textbox that might be used for displaying longer strings that might be able to include formatting?

 

[Fluff]

You can do it for a Textbox on a sheet, but you need to loop through the characters like

   Dim i As Long
   With Sheets("B").Shapes("TextBox 1").TextFrame
      .Characters.Text = ActiveCell.Value
      For i = 1 To Len(ActiveCell.Value)
         .Characters(i, 1).Font.Color = ActiveCell.Characters(i, 1).Font.Color
      Next i
   End With