VBA store formatted text in clipboard

Question

I need to copy/store a string of text into the clipboard but need that text to be formatted (font type, color, weight, etc.)

Private Sub copyToCB(varText As String)
    Dim x As Variant
    x = varText

    CreateObject("htmlfile").parentWindow.clipboardData.setData "text", x
End Sub

The above does the job of storing the referred text into the clipboard but it's stored as plain text. I'd like it to be e.g. bold and red.

I've been scouring the Internet literally for hours, to no avail. You'd think this would be something straightforward but I'm at a total loss!

A `String` has no formatting - where is the bold/font color coming from? — Tim Williams, May 17 '22 at 17:58
That's kinda the point. I WANT to format the text with font color, size, family, etc. but it can't be done with a plain string. So, how TO do it? — Michael, May 17 '22 at 18:10
Once you have pasted the string in excel, use the macro recorder, format it in the way that you want and look at the code that it gives in order to do so. However, if you want to paste it as it is from the html file, use the method ["Paste_from_clipboard" from this site](https://www.exceldemy.com/vba-paste-from-clipboard-to-excel/) or just sendkeys as control v as specified there as well. — Sgdva, May 17 '22 at 18:13
In that case a little more detail/context such as where the final paste is going to might be useful. — Tim Williams, May 17 '22 at 18:16
@TimWilliams: It shouldn't matter where it's coming or going. I want to put a FORMATTED slab of text into the clipboard to do with what I want. I may need to paste it into an email, into a textarea in a browser, into a WordPad document, whatever... I know there are workarounds, such as copying formatted text directly from Word, for instance. My question though is very simple: Is there a way to use a simple function/sub in V.B.A. that can format a string and store it in the clipboard? — Michael, May 17 '22 at 18:25

Tim Williams · Accepted Answer · 2022-05-17T20:10:48.513

1

If you use the clipboard classes from @GMCB found at https://stackoverflow.com/a/63735992/478884

You can do this:

Sub TestCopying()
    CopyWithSomeFormatting "This should paste as red/bold"
End Sub

Sub CopyWithSomeFormatting(txt As String)
    Dim myClipboard As New vbaClipboard 'Instantiate a vbaClipboard object
    myClipboard.SetClipboardText _
        "<span style='color:#F00;font-weight:bold'>" & txt & "</span>", "HTML Format"
End Sub

Works for me at least when pasting to Word/Excel

edited May 17 '22 at 20:10

answered May 17 '22 at 18:46

Tim Williams

154,628
8
97
125

YES, that does it but, good gracious, what a massive pain it is! You'd THINK something so basic would be easily done with just a couple of lines of code! THANK YOU! – Michael May 17 '22 at 20:09
@Michael if this solved your problem, please accept answer so it helps others to identify that this was solved :) – Sgdva May 19 '22 at 15:39

VBA store formatted text in clipboard

1 Answers1