Accessing OLEObject events using custom class

Question

I am trying to create a custom class in Excel VBA to handle the events GotFocus and LostFocus for an OLEObject (ActiveX Control on a worksheet).

custom class clsSheetControl

Dim WithEvents objOLEControl as OLEObject

Public Sub Init(oleControl as OLEObject)
    Set objOLEControl = oleControl
End Sub

end custom class

calling worksheet

Public Sub SetControlHandler()
     set clsControl = new ClsSheetControl
     clsControl.Init(Me.OLEObjects("cmdControl1")
End Sub

end worksheet

When I select the objOLEControl in the dropdown, I am able to create "GotFocus" and "LostFocus" in the custom class module, however when the line

    Set objOLEControl = oleControl

is encountered in the custom class, I get the error

"459: Object or class does not support this set of events".

I tried searching for the answer but most of the results deal with accessing the control object within the OLEObject, not what I am trying to do here.

EDIT

This doesn't work on the worksheet either

Worksheet

Dim WithEvents objCtrl As OLEObject
Dim WithEvents chkCtrl As MSForms.CheckBox

Private Sub Worksheet_Activate()
     Set chkCtrl = Me.OLEObjects("chkControl").Object
     Set objCtrl = Me.OLEObjects("chkControl")
End Sub

Private Sub chkControl_GotFocus()
    MsgBox ("chkControl has focus")
End Sub

The line

Set objCtrl = Me.OLEObjects("chkControl")

raises the same error. However accessing the GotFocus event directly (the chkControl_GotFocus event) is fine.

Another thing to note is I can access the GetFocus event of the OLEObject in the worksheet code. — Bobsickle
– Bobsickle, Commented May 25, 2012 at 22:51

Tim Williams · Accepted Answer · 2012-05-25 23:19:49Z

0

This worked for me, but it's specific to Textbox controls and has no "GotFocus/LostFocus" events...

clsSheetControl

Dim WithEvents objOLEControl As MSForms.TextBox

Public Sub Init(oleControl As MSForms.TextBox)
    Set objOLEControl = oleControl
End Sub

Private Sub objOLEControl_Change()
    MsgBox "Changed"
End Sub

Private Sub objOLEControl_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
                                  ByVal Shift As Integer)
    MsgBox "Key down: " & KeyCode
End Sub

Worksheet

Dim objControl As clsSheetControl

Public Sub SetControlHandler()
    Set objControl = New clsSheetControl
    objControl.Init Me.OLEObjects("TextBox1").Object
End Sub

answered May 25, 2012 at 23:19

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

Bobsickle Over a year ago

Thanks for your answer but I don't think you have read my question properly - I don't need to access the control and it's associated events contained in the OLEObject, I know how to do that already. The point of my question is that I want to access the OLEObject GotFocus and LostFocus events. These are accessable in the worksheet, but I am not able to access them using a WithEvents OLEObject member in a custom class module.

Tim Williams Over a year ago

You're not the first one to have this issue (eg. see stackoverflow.com/questions/6390289/…) It does not seem possible to define a generic OLEObject "WithEvents" and then assign a worksheet control to it. Almost as if the events of both the container object and the control itself are merged: if you declare a variable of type MSForms.Textbox then it has no Got/Lost_Focus events, whereas the "same" object embedded on a worksheet does (in addition to the other textbox-specific events).

Collectives™ on Stack Overflow

Accessing OLEObject events using custom class

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related