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

[pflipper]

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

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

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

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?

 

[pflipper]

Anyone??!

 

[Jaafar Tribak]

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

 

[pflipper]

Tried that - still get the same error message - Run-time error 1004, Unable to get Object Property of OLEObject class.