Excel 2010: Where is Microsoft Forms 2.0 Object Library?

[RexSwain]

I have a nice little macro

' Copy sum of selected cells into clipboard
Dim MyDataObj As New DataObject
MyDataObj.SetText Application.Sum(Selection)
MyDataObj.PutInClipboard

But it does not work in my new Excel 2010.

Apparently I need the Microsoft Forms 2.0 Object Library,
but that's not in the list at Tools|References !

Any suggestions...?

 

[Colin Legg]

As a test, try to add a userform to your project. If successful, a reference to that library will be automatically added for you.

 

[RexSwain]

I can add a UserForm.

But I still get "Compile error: User-defined type not defined" when I try to run my macro.

 

[Colin Legg]

Once the userform has been added, has a reference to the Microsoft Forms 2.0 Object Library been added?

 

[RexSwain]

No, no change to the list of references.

 

[Colin Legg]

Search your PC for the FM20.DLL file.

On my PC it is under C:\WINDOWS\system32

 

[RexSwain]

That's the ticket! Manually adding FM20.DLL solves the problem -- now Microsoft Forms 2.0 Object Library appears in my list of references, and my macro works. Thanks very much for the name of the DLL -- that was the missing link.

 

[GregNorris]

Thanks Rex for finding that file. I've tried to get the clipboard code working for hours. I guess it's simple when you know how.

 

[And3as]

Hi there,
I am an Excel VBA enthusiast and really struggling with this concept of the "FM20.dll" file.
I found the DLL on the computer by doing a general search and pasted in the C:\Windows\System32 dir. I restarted Excel, added a userform (and tried deleting it again as well) - No change, still not able to use DataObject to copy cell string to clipboard.

So here is my scenario:
I created my own invoice sheet. The file will be saved as a PDF to send to clients. This PDF's name will vary depending on the information in the excel sheet. Info is gathered from different cells and added (as variables) together with static text to a single cell. The value of this cell (Not including the formula) must be copied to the clipboard so that when I press F12 to "save as", and choose the directory I only have to Ctr+V to paste the correct file name and click save.
The cell value (File Name) must be updated with every change in that sheet in order for it to have the latest details from the sheet for the name of the file.

So in short: I need to copy only the value of a cell as a text string to the clipboard to be available to be pasted as text as if you copied it from a MS Word doc - This cell value contains formula and text.

The formula I have tried includes the following:
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyData As DataObject
Set MyData = New DataObject

MyData.SetText (Range("O1:W1"))
MyData.PutInClipboard

End Sub
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""


Any help would be much much appreciated.
Thanks in advance.

Andrew

 

[RexSwain]

Edit your macro. On the VBA form, select Tools | References. Use the Browse button to add FM20.DLL.