Richard Schollar
MrExcel MVP
- Joined
- Apr 19, 2005
- Messages
- 23,707
Andrew Paulsom supplied code to add a combobox and programmatically add code to its events here:
http://www.mrexcel.com/forum/showthread.php?t=281230
However, I would like to investigate using a class module to hold a WithEvents object that could hold pre-determined event code. I would like to create the combobox on the sheet, then assign this combobox via a Property Set statement to my Class. I am having great difficulty doing so, however. This is the code I have tried so far:
I believe it is erroring out at the Set objClass.obj = cbo line (but I am unable to debug due to adding an ActiveX control to the sheet with stops me from Stepping).
I get a Type Mismatch error here (presumably because an OLEObject is not a MSForms.Combobox) but I have tried variations (including using OLEObject as the declaration in both Class and standard module) and I can get nothing to work.
Can someone put me out of misery and tell me where I'm going wrong (or let me know if it isn't possible)?
Thanks.
http://www.mrexcel.com/forum/showthread.php?t=281230
However, I would like to investigate using a class module to hold a WithEvents object that could hold pre-determined event code. I would like to create the combobox on the sheet, then assign this combobox via a Property Set statement to my Class. I am having great difficulty doing so, however. This is the code I have tried so far:
Code:
'Standard module:
Sub test()
Dim cbo As OLEObject, objClass As Class1
Set cbo = ActiveSheet.OLEObjects.Add("Forms.Combobox.1")
Set objClass = New Class1
Set objClass.obj = cbo
End Sub
'Class module:
Dim WithEvents cbo As msforms.ComboBox
Property Set obj(Combo As Variant)
Set cbo = Combo
End Property
Private Sub cbo_Change()
ActiveSheet.Range("A1").Value = cbo.Value
End Sub
I believe it is erroring out at the Set objClass.obj = cbo line (but I am unable to debug due to adding an ActiveX control to the sheet with stops me from Stepping).
I get a Type Mismatch error here (presumably because an OLEObject is not a MSForms.Combobox) but I have tried variations (including using OLEObject as the declaration in both Class and standard module) and I can get nothing to work.
Can someone put me out of misery and tell me where I'm going wrong (or let me know if it isn't possible)?
Thanks.