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

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

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

Accessing OLEObject events using custom class

1 Answers