Paste and keep source formatting

Trying to fathom out how to paste from clipboard (contains Excel data) into a textbox in word using VBA, whilst using the word option of "keeping the source formatting".

Code:

---

Dim Clipboard As MSForms.DataObject
    Set Clipboard = New MSForms.DataObject
    Dim myDataObject As DataObject
   
    Set myDataObject = New DataObject
    myDataObject.GetFromClipboard


Clipboard.GetFromClipboard
       
        TextBox3.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

---

Many thanks!

What sort of text box? Some text boxes by definition will only contain text, and you have not indicated whether you are running the code from Word or Excel, though I assume Excel
A simple Paste command will by default keep the Excel formatting, provided the receptacle can contain it. If you need a receptacle then I would suggest using a rich text content control, then if that is titled TextBox3 the following will work subject to provisos

Code:

---

Sub PasteToWord()
Dim wdApp As Object
Dim wdDoc As Object
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo err_Handler
    Set wdDoc = wdApp.Activedocument
    wdDoc.SelectContentControlsByTitle("TextBox3").Item(1).Range.Paste
lbl_Exit:
    Exit Sub
err_Handler:
    MsgBox "The content control 'TextBox3' is not present in the current document", vbCritical
    Err.Clear
    GoTo lbl_Exit
End Sub

---

The Clipboard code from your macro is superfluous, and the example assumes that the document is open and active in Word and that the data has been copied to the clipboard.

Thanks, gmayor for taking the time out to look at this for me.

I think I need to clarify a few bits of information that were ommitted from my original post.


The data that I need to transfer from Excel to Word is generated in Excel and is put into the clipboard from the same application. I have ensured that everything that I need to copy over is present and correct by opening a blank Word document and then pasting the contents into this by using the default paste button, selecting the "keeping the source formatting" option.

Both applications will be open and both will be using a VBA UserForm to perform their respective tasks.

The TextBox I need the content to arrive in (Word), is a UserForm TextBox which is currently called "TextBox3". It is Word that will be producing the final document.

The best that I have managed to achieve is the content being "copied" into TextBox3 but minus the formatting. Even copying from Excel and then pasting into the TextBox3 loses the required bold formatting.

The most frustrating thing is that the important formatting that I need to keep is certain pieces of text that are bold.

What you are trying to achieve is not possible. A userform text box does not support text formatting.

Thank you for putting me out of my misery. Thought this might be the answer.

I think that I had probably stumbled across this fact myself when I couldn't paste with formatting directly into the TextBox.

This will leave your original suggestion and code. You mention that it "will work subject to provisos". What are these please?

The provisos are under the code.

Ah yes, I'm with you.

This works superbly, thanks!