How to Insert OLEObject via User Defined Formula

[spetty]

Hi

I need to insert an OLEObject (in this case a pdf file) into a worksheet via a formula entered into a cell.

I have a sub InsertPDF that does the job fine when run by itself. I have a function PDF_Added that calls that sub with no problem if the function is in turn run from another sub. However, I need to make the PDF_Added function run when entered into a cell as =PDF_Added(). This results in this error message: Unable to get the Add property of the OLEObjects class. Can anyone see a way to add an OLEObject to the worksheet via user defined formula?

Any help much appreciated, thanks.
Sam

Sub InsertPDF()
    Dim pdf As OLEObject
    
    Set pdf = ActiveSheet.OLEObjects.Add( _
            fileName:="C:\Documents and Settings\spetty\Desktop\pdf1.pdf", _
            Link:=False, _
            DisplayAsIcon:=True, _
            IconFileName:="C:\WINDOWS\Installer\{AC76BA86-7AD7-1033-7B44-A71000000002}\PDFFile.ico", _
            IconIndex:=0, _
            IconLabel:="myCaption")
        
End Sub


Function PDF_Added() As Boolean

    PDF_Added = False
    
    On Error Resume Next
    
    Call InsertPDF
    
    If Err = 0 Then
        PDF_Added = True
        Err = 0
    End If

End Function

[Norie]

Sam

It isn't possible to change a worksheet using a UDF on a worksheet, and I kind of think inserting an OLE object might just come under change.

[Kyle123]

I doubt that you can, Excel won't let you do a lot of things through a worksheet function mainly relating to adding to/altering the structure of the workbook or other cells

[mikerickson]

Cell formulas cannot change the formatting of worksheets or insert objects.

[Kyle123]

Well I guess that gives you a universal consensus then