Error with OLEObjects in vba code (Excel 2010)

Question

For a document I'm using multiple databases (in seperate worksheets) and multiple dropdown menus (combo boxes) which are populated by those databases. On change it's adding some information and a picture to the main sheet, which is retrieved from those databases. All works well, but... Once I try to change a cell in one of the databases (doesn't matter which one), it's giving me an error with the dropdown boxes:

"Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed"

(Debug highlights on the line with .OLEObjects)

What am I overseeing here? Any help is much appreciated!

Private Sub CDL1_change()

Dim c As Range, MyPath, MyFile As String, WS As Worksheet
MyPath = Sheets("TeamChart").Range("C95").Value
Set WS = ActiveSheet
    With Worksheets("CDL").Range("rngCDL")
        Set c = .Find(CDL1.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not c Is Nothing Then
                Sheets("TeamChart").Range("L5") = c.Offset(0, 0).Value
                Sheets("TeamChart").Range("M6") = c.Offset(0, 1).Value
                Sheets("TeamChart").Range("M7") = c.Offset(0, 2).Value
                MyFile = c.Offset(0, 3).Value
                    With WS
                    .OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
                    End With
            Else
                Sheets("TeamChart").Range("L5") = "Not Found"
                Sheets("TeamChart").Range("M6") = "Not Found"
                Sheets("TeamChart").Range("M7") = "Not Found"
            End If
    End With

End Sub

This is cell C95's value: G:\xxx\Fotos\. I've already tried it with MyFile as being both the path and the file name, gave the same results.

Sure, sorry. Would have been handy to do that in the first place :) The error message is: "Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed" and with debug it highlights the line with .OLEObjects. I hope you can make me a bit wiser here :) — RobK
– RobK, Commented Aug 11, 2014 at 14:30
Complete guess, but could it be that (MyPath & MyFile) is not a valid path because either MyPath doesn't end with a backslash or MyFile doesn't start with one? Perhaps it should be (MyPath & "\" & MyFile)? — djikay
– djikay, Commented Aug 11, 2014 at 18:50
@djikay that seems really possible, but I'm not 100% sure "\" will always work (could be "/" too) ... so maybe use Application.PathSeparator — smagnan
– smagnan, Commented Aug 12, 2014 at 6:38
@RobK It could be great if (cf djikay comment) you could add some of your worksheet information (like Cell C95 in TeamChart: Sheets("TeamChart").Range("C95").Value etc ...) — smagnan
– smagnan, Commented Aug 12, 2014 at 6:41

Josh Crozier · Accepted Answer · 2015-12-11 02:08:10Z

2

I finally managed to get the same error (and many others by the way ^^) and to resolve it (and all the other error)

To help you solve your case I'm going to put the example I used:

So This code works perfectly:

Private Sub testOleobj()

    Dim MyPath, MyFile As String, WS As Worksheet
    MyPath = Sheets("testSh1").Range("C95").Value  ' A path equivalent to G:\xxx\Fotos\ for me
    Set WS = Sheets("testSh1")    ' Here is the change
    MyFile = "test.jpg"
    With WS
        .OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
    End With 

End Sub

But this code does not work (on purpose):

Note: I execute this code from, let's say a sheet called "sheet1"

Private Sub testOleobj()

    Dim MyPath, MyFile As String, WS As Worksheet
    MyPath = Sheets("testSh1").Range("C95").Value  ' A path equivalent to G:\xxx\Fotos\ for me
    Set WS = ActiveSheet
    MyFile = "test.jpg"
    With WS
        .OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile)
    End With 

End Sub

I am sure the path and the image are both valid because I they are not I will get an other error (Error 481 -> here Invalid image ) BUT

I get "Run-time error '1004': Method 'OLEObjects' of object '_worksheet' failed" and .OLEObjects("picCDL").Object.Picture = LoadPicture(MyPath & MyFile) is the line which causes the error ... why?

Because the picture will be loaded in picCDL which is for me in sheet "testSh1" but I try to find it in ActiveSheet which corresponds to ... "sheet1"

To conclude:

I think it's the same for you (but I would have needed more of you workboot info to be sure): You use Set WS = ActiveSheet but my theory is .OLEObjects("picCDL").Object.Picture does not actually exist on this sheet (the active sheet actually, that's why it fails) but is on an other sheet so you should not use ActiveSheet but the direct reference like in my second example.

I hope this will resolve your issue, If not please add more information about your workbook, the worksheets you use etc ...

Last note: Why use With WS since you just set one property? (I used it too to match you code as much as I can but it has no use)

edited Dec 11, 2015 at 2:08

Josh Crozier

242k56 gold badges401 silver badges316 bronze badges

answered Aug 13, 2014 at 6:55

smagnan

1,26718 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RobK Over a year ago

Bingo! You're the best. I've deleted the With WS entirely and you are right: it works like a charm! Thank you so much! :)

Patrick Lepelletier Over a year ago

bravo on referencing worksheets. Too bad you didn't have a clue on wich worksheet to reference...

Collectives™ on Stack Overflow

Error with OLEObjects in vba code (Excel 2010)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related