'can't find a way to handle events on Msforms.combobox from oleobject
I hope you're doing well
I created dynamically a combobox, as following
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oSh As Shape
Set oSh = ActiveSheet.Shapes.AddOLEObject(Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height, ClassType:="Forms.ComboBox.1")
With oSh.OLEFormat.Object
Debug.Print .OLEType
.Visible = True
.Name = "test"
.ListFillRange = "Sheet1!C1:C" & Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).row
.Activate
End With
Next, I want to handle events for each combobox. So after searching on the net, the suitable solution was to create a class named classe1, which has the following code :
Option Explicit
Public WithEvents cbEvents As MSForms.ComboBox
Private Sub CbEvents_Enter()
do something
End Sub
And then in the main module, I added
Set objMyEventClass.cbEvents = oSh.OLEFormat.Object
I get an incompatible type error
Can anyone help ? thanks
EDIT:
Thanks to the persons who comment, so when I change
Set objMyEventClass.cbEvents = oSh.OLEFormat.Object
to
Set objMyEventClass.cbEvents = oSh.OLEFormat.Object.Object
I have no longer errors, but I can't handle any event (I tired to change _Enter to _Change but it doesn't work)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
