VBA Excel OleObject Event LostFocus fails to work

vanzieto · April 9th, 2009, 05:51 PM

Hello,

in my excel sheet I try to create oleObject Controls as the user clicks certain cells.

Creating them is no problem:

Code:

'Excel sheet object

Set oleObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
oleObj.Left = rIntersect.Left
oleObj.Top = rIntersect.Top
oleObj.Width = rIntersect.Width
oleObj.Height = rIntersect.Height
oleObj.LinkedCell = rIntersect(1, 1).AddressLocal(, , xlA1)

Then I try to link the created oleObject to an instance of a class which enables eventhandling:

Code:

'Class module cOleObjectEvent

Public WithEvents myObj As OLEObject

Private Sub myObj_LostFocus()
     myObj.Delete
End Sub

Code:

'Excel sheet object

If OleObjEvent Is Nothing Then Set OleObjEvent = New cOleObjectEvent
Set OleObjEvent.myObj = oleObj
        
oleObj.Activate

When the code runs, it generates an error at the line where I try to make the link to the instance of the event handling class, giving the error message "Object or class does not support the set of events"

Any suggestions would be very helpfull

Thanx,

Tom