In VBA I'm trying to paste from a rich text control (RichTx32.ocx) to a
worksheet (Excel 2007) cell but the text always comes out as the unformatted
RTF. How do I paste it so that it appears a properly formatted text?

The code:
Sub RFTTest()
Dim richTextString As String
richTextString = UserForm1.RichTextBox1.TextRTF

Dim myDataObject As New dataObject
Set myDataObject = New dataObject

myDataObject.SetText richTextString ', &HBF01 '(This flag doesn't work
either)
myDataObject.PutInClipboard

Worksheets("Sheet1").Range("D3").Select
Selection.PasteSpecial
End Sub

George,

I don't have that control, but I think you need to copy the rich text to a clipboard object as rich
text, and paste from there. Your richTextString string variable probably dumps out all the
formatting information.

I'm sure that you can google up some examples...

HTH,
Bernie
MS Excel MVP


"George Lee" wrote in message
...
In VBA I'm trying to paste from a rich text control (RichTx32.ocx) to a
worksheet (Excel 2007) cell but the text always comes out as the unformatted
RTF. How do I paste it so that it appears a properly formatted text?

The code:
Sub RFTTest()
Dim richTextString As String
richTextString = UserForm1.RichTextBox1.TextRTF

Dim myDataObject As New dataObject
Set myDataObject = New dataObject

myDataObject.SetText richTextString ', &HBF01 '(This flag doesn't work
either)
myDataObject.PutInClipboard

Worksheets("Sheet1").Range("D3").Select
Selection.PasteSpecial
End Sub

The DataObject only accepts plain text, so that's not going to work (no idea
what you are trying to do with that flag).

It's a long time since I've done anything with the RichTextBox, but from
memory even manually copying the text to the clipboard and then into a cell
doesn't carry formats. However you can copy formats to say Word or even an
RTF, then copy to Excel. You can also write text to file with formats, eg

Dim FF As Integer
Dim rtb As RichTextBox

FF = FreeFile

Set rtb = Me.RichTextBox1
Open "RTBtext.rtf" For Output As #FF
Print #FF, rtb.TextRTF
Close #FF

You could then open the file in Word, copy and paste to Excel.

I know that doesn't help you directly, and pretty sure there's an API
clipboard method, but perhaps some ideas for further search.

Regards,
Peter T



"George Lee" wrote in message
...
In VBA I'm trying to paste from a rich text control (RichTx32.ocx) to a
worksheet (Excel 2007) cell but the text always comes out as the
unformatted
RTF. How do I paste it so that it appears a properly formatted text?

The code:
Sub RFTTest()
Dim richTextString As String
richTextString = UserForm1.RichTextBox1.TextRTF

Dim myDataObject As New dataObject
Set myDataObject = New dataObject

myDataObject.SetText richTextString ', &HBF01 '(This flag doesn't work
either)
myDataObject.PutInClipboard

Worksheets("Sheet1").Range("D3").Select
Selection.PasteSpecial
End Sub