Excel 2003/7 issue, Run-time error 1004, Unable to get Object Property of OLEObject class

pflipper

New Member
Joined
Apr 6, 2010
Messages
3
Hi all,

First post here...I hope I've come to the right place...

I have an Excel sheet with an Add-in that worked fine in 2003 until someone opened it up with Excel 2007. Now, I get an error in both 2007 and 2003.

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

The error occurs as I'm looping through the objects on a sheet that becomes the active sheet, and assigning a combo box to an object. Here's the code

In a class module : CExcelEvents

Code:
Public Sub WBk1_SheetActivate(ByVal Sh As Object)
    Set WKs = Sh
    Call EnableCombo
    ... other code
End Sub


Code:
Public Sub EnableCombo()
Dim obj As Object
 
    Set gclsSheet = New CSheetEvents
  
    For Each obj In WKs.OLEObjects
        Select Case obj.Name
            Case "cboSort1"
--->  Error happens twice, when setting object
                Set gclsSheet.SortCombo1 = WKs.OLEObjects("cboSort1").Object
            Case "cboSort2"
                Set gclsSheet.SortCombo2 = WKs.OLEObjects("cboSort2").Object
        End Select
    Next obj
    ' get last open row value on sheet with activation, assign to global lastrow value
    lastShtRow = LastRowVal(WKs)
End Sub


In a class module : CSheetEvents

Code:
Public WithEvents CboSort1 As ComboBox
Public WithEvents CboSort2 As ComboBox

Public Property Get SortCombo1() As ComboBox
    Set SortCombo1 = CboSort1
End Property

Public Property Set SortCombo1(objProductCombo1 As ComboBox)
    Set CboSort1 = objProductCombo1
End Property

Public Property Get SortCombo2() As ComboBox
    Set SortCombo2 = CboSort2
End Property

Public Property Set SortCombo2(objProductCombo2 As ComboBox)
    Set CboSort2 = objProductCombo2
End Property

Anyone able to help with this?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Anyone??!

Not sure but try changing the var type of the SortCombo1 Property to Object and see if it works:


Public Property Set SortCombo1(objProductCombo1 As Object)

Public Property Get SortCombo1() As Object
 
Upvote 0
Tried that - still get the same error message - Run-time error 1004, Unable to get Object Property of OLEObject class.

:rolleyes:
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top