Pasting issues

[Sir Babydum GBE]

Hi

Hi how do I accomplish the following please:

Range("A1").FormulaR1C1 = ...

I want the FormulaR1C1 to return the current text in whatever's in the clipboard. It may not always be a simple text string in the clipboard (for example it me be that someone's copied a merged cell where normal pasting returns a "cannot change part of a merged cell" error - or it may be that there's html on the clipboard.

Is this doable?

Thanks

[rory]

You could try something like this - it requires a reference to the MSForms object library:
Dim objData As MSForms.DataObject
Set objData = New MSForms.DataObject
objData.GetFromClipboard
ActiveCell.Formula = Application.Clean(objData.GetText)

[Sir Babydum GBE]

How do I get that reference please?

[rory]

In the VB Editor, select Tools-References from the menu then select from the list. You can also insert and then delete a userform.

[Sir Babydum GBE]

In the VB Editor, select Tools-References from the menu then select from the list. You can also insert and then delete a userform.

Is this something that would need to be done on each machine? or once it is is done on a particular workbook, it will always work on that workbook? Also - I can't find a reference "MSForms" in my list of available references...?

Cheers

[rory]

It would be "Microsoft Forms 2.0 Object Library". Once set, it should work on any computer with VBA installed.

[Sir Babydum GBE]

That worked great thanks!