Run-time error 1004, Unable to get Object Property of OLEObject class

[MarcPop]

Hi,
I hope that someone knows the solution to this one.
I have written a macro in excel 2007 which runs a database query with some user selected criteria and consequently fills some graphs and tables on a sheet.
As part of the project it also updates and adds items in a couple of OLEObjects (comboboxes).​

This is working fine and I tested on different other pc’s. On one pc however it does not work and crashes on the code referring to the OLEObjects with the error: ​

Run-time error 1004, Unable to get Object Property of OLEObject class​

It crashes on the first line of the following code:​

Set cboTemp = ws.OLEObjects("ComboSalesman")​

With cboTemp
.ListFillRange = "Salesman"
.Object.ListIndex = 0
End With​

After this more code follows referring to other objects and if I test those the same error appears on that one pc only. Of course this is the pc of the designated user of the report L.
On all pc’s I am using Excel 2007. I have checked to see if maybe some libraries (references) where not set on the pc where the code is not working but that seems fine (as it should be).​

What can cause this code to crash on one pc only?
Help is much appreciated!
Marc<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

 

[Ruddles]

I had a similar experience with a Word document embedded in a worksheet: it worked on my office machine and a friend's machine but not on my home machine. After some Googling I came across advice on MSDN to refer to the object by its index number, so:-

[FONT=Verdana][COLOR=black]Set cboTemp = ws.OLEObjects(1)[/COLOR][/FONT]

If you don't know the index number, you might need to do something like this to find out:-

For Each cboTemp In ws.OLEObjects
   Debug.Print cboTemp.Index, cboTemp.Name
Next cboTemp

(Untested.)

That actually didn't work for me but someone else suggested .Activateing the object first, and that did work.

Try:-

ws.OLEObjects("ComboSalesman").Activate
Set cboTemp = ws.OLEObjects("ComboSalesman")

Just a shot in the dark...

 

[MarcPop]

Thanks for your reply Ruddles.
Unfortunately it didn't work.
I already tried using the index number and that didn't work.

When activating the object first, I get the following error :
1004: Cannot activate source file object.

Both options of course work on other pc's.
It seems excel does not seem to be able to locate the Oleobjects on this pc.
To make it more interesting, when logged in on this pc with my own username it works fine again.

 

[shg]

How about ...

    With Sheet1.ComboSalesman
        .ListFillRange = "Salesman"
        .ListIndex = 0
    End With

 

[MarcPop]

How about ...

    With Sheet1.ComboSalesman
        .ListFillRange = "Salesman"
        .ListIndex = 0
    End With

Thanks shg4421, but unfortunately this didn't work either.
Same error message as earlier:​

Run-time error 1004, Unable to get Object Property of OLEObject class​

 

[Andrew Poulsom]

Are the ActiveX controls enabled?

http://office.microsoft.com/en-us/e...controls-in-office-documents-HA010031067.aspx

 

[shg]

Works for me in both 2003 and 2007 on XP. I reckcon that's not a surprise considering you only have a problem with one computer.

Shooting in the dark, and a variation on Ruddles suggestion,

    With Sheet1.OLEObjects(Sheet1.ComboSalesman.Index)
        .ListFillRange = "Salesman"
        .Object.ListIndex = 0
    End With

 

[MarcPop]

Are the ActiveX controls enabled?

http://office.microsoft.com/en-us/e...controls-in-office-documents-HA010031067.aspx

That's it.
I have replicated the errors on my pc now by setting the activex controls to -turn off without warning- .
It went through my head to check the security settings on the other pc but a voice in my head said: "nehhhh, that can't be it".
Lesson learned here.
Thanks for your help Andrew (and all others for their effort).

 

[shg]

Well done, Andrew.