3

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.

Community
  • 1
  • 1
Bobsickle
  • 1,689
  • 1
  • 12
  • 15
  • Another thing to note is I **can** access the GetFocus event of the OLEObject in the **worksheet** code. – Bobsickle May 25 '12 at 22:51

1 Answers1

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
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 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. – Bobsickle May 25 '12 at 23:35
  • You're not the first one to have this issue (eg. see http://stackoverflow.com/questions/6390289/how-to-assign-an-event-to-multiple-objects-with-excel-vba) 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). – Tim Williams May 26 '12 at 07:00