Copy Contents of the Formula Bar - not the Cell

[mstuf]

Good Evening -

I need a Macro that when run from the Active Cell, that would Highlight and Copy the <b>Entire</b> Formula Bar Contents to the Clipboard ?

I found a past thread here at Mr Excel:

http://www.mrexcel.com/forum/showthread.php?t=192384

It gives what appears to be a Solution to a similar need.

   Sub TryThisFirst()
   Dim DatObj As New DataObject
   DatObj.SetText ActiveCell.Text
   DatObj.PutInClipboard
End Sub

'add a reference to Microsoft Forms 2.X Object Library

I am not sure how to "add a reference to Microsoft Forms 2.X Object Library" to try this out. As is, it nets a Compile error at the Dim Statement.

Is this Code what I need ? Am I on the Right Track ?
Help and Explanation much appreciated. Or if there is a simpler way ?

Thanks as Always -- Your help appreciated much !!

 

[shg]

An easy way to establish a reference is to add and then delete a userform.

Sub TryThisFirst()
    Dim oDO      As MSForms.DataObject
    
    Set oDO = New MSForms.DataObject
    oDO.SetText ActiveCell.Formula
    oDO.PutInClipboard
End Sub

 

[Jaafar Tribak]

You can interface the IDataObject interface at run time thus without the need to add a temp userform to your project.

use this routine :

Private Sub PutCellFormulaInClipBoard(ByVal Cell As Range)

    Dim oDataObject As Object
    
    Set oDataObject = _
        GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With oDataObject
        .Clear
        .SetText Cell.Cells(1).Formula
        .PutInClipboard
    End With
    
End Sub

Usage:

The code below will place the Formula of the activecell into the clipboard.

Sub Test()
    PutCellFormulaInClipBoard ActiveCell
End Sub

 

[mstuf]

Thank You Shg

That Netted a Compile Error " User-defined type not defined "

Looking at Google I found similar error messages that say I need to "Set a Reference" --


Is that correct and can you tell me how ?

Thanks

 

[mstuf]

Jaafar Tribak --- thank you

I had never used a private sub before and it took some reading to figure out how to invoke it but once I grasp the method I was able to incorporate it into my routine.

Works Great ! Very Happy !! It will get used approx 70 times a week.

As I learn new stuff here -- it opens my eyes to more things I can do.



Shg -- Thank you for the Help too -- Jaafar Tribak's method is installed and working. Sorry that I didn't understand how to Set the Reference -- It gives me another thing to search and read during dinner. I will continue to read and try to learn.