Add OLEObjects to a userform

Question

I would like to do the something very similar to what is done in the answer given to this post. The only difference is that I would like to add the OLEObjects to a userform, not to a worksheet.

EDIT 1 : I have tried UserForm1.OLEObjects.Add("Forms.ComboBox.1")

EDIT 2 : I post the entire code here :

'this is public so it doesn't go out of scope
Public gclsControlEvents As CControlEvents

Sub Bouton1_Click()
    Call MakeCombo
End Sub



Sub MakeCombo()

    Dim oleCbx As OLEObject

    'Create the combobox
    Set oleCbx = UserForm1.Controls.Add("Forms.ComboBox.1") 'Bug at this line
    oleCbx.Object.AddItem "1"
    oleCbx.Object.AddItem "2"

    'hookup the events
    Application.OnTime Now, "HookupEvents"

End Sub

Sub HookupEvents()

    Set gclsControlEvents = New CControlEvents
    Set gclsControlEvents.Cbx = UserForm1.OLEObjects(1).Object

End Sub

EDIT 3 : Here is the class code :

Private WithEvents mclsCbx As MSForms.ComboBox

Public Property Set Cbx(ByVal clsCbx As MSForms.ComboBox): Set mclsCbx =    clsCbx: End Property
Public Property Get Cbx() As MSForms.ComboBox: Set Cbx = mclsCbx: End Property

Private Sub mclsCbx_Change()

MsgBox Me.Cbx.Name

End Sub

Isn't it almost identical to the post you quote but with the OLEObject added to the userform instead of the worksheet? You've answered your own question, I think. — stucharo, Jul 23 '15 at 08:56
But how do you add it to a userform, I've tried `UserForm1.OLEObjects.Add("Forms.ComboBox.1")` but it doesn't work. — otus, Jul 23 '15 at 09:01

Rory · Accepted Answer · 2015-07-23T10:29:09.613

2

You need to replace all references to OLEObjects with Controls and the variable type is MSForms.Control not OLEObject, so:

Sub MakeCombo()

    Dim oleCbx As MSForms.Control

    'Create the combobox
    Set oleCbx = UserForm1.Controls.Add("Forms.ComboBox.1")
    oleCbx.AddItem "1"
    oleCbx.AddItem "2"

    'hookup the events
    Application.OnTime Now, "HookupEvents"

End Sub

I haven't looked up the class code but that will require similar changes.

For a userform, there is no need for OnTime since the project doesn't get reset and your variables don't go out of scope. All you need is the class, and then in the userform itself you can put something like this:

Option Explicit

'this is public so it doesn't go out of scope
Public gclsControlEvents As CControlEvents

Private Sub UserForm_Initialize()

    Dim oleCbx As MSForms.ComboBox

    'Create the combobox
    Set oleCbx = Me.Controls.Add("Forms.ComboBox.1") 'Bug at this line
    oleCbx.AddItem "1"
    oleCbx.AddItem "2"

    Set gclsControlEvents = New CControlEvents
    Set gclsControlEvents.Cbx = oleCbx

End Sub

No other code is necessary.

edited Jul 23 '15 at 10:29

answered Jul 23 '15 at 09:30

Rory

32,730
5
32
35

Thanks, I also have a problem in the HookupEvents sub, I tried to replace `Set gclsControlEvents.Cbx = UserForm1.OLEObjects(1).Object` by ` Set gclsControlEvents.Cbx = UserForm1.Controls.Item ` but it doesn't work, can you please look at the code of the classe module in my edit 3, I'm a beginner in vba and the changes to make are not obvious to me @Rory – otus Jul 23 '15 at 09:57
`Userform1.Controls(1)` or name it when you add it and use the name. I'd also point out that you don't need to use `OnTime` here - adding controls to a form doesn't reset your project. Ideally I'd put the code in the userform - there are many examples of this sort of thing with forms - you have not chosen a good base to start from! ;) – Rory Jul 23 '15 at 10:19
Ok, I tried ` Set gclsControlEvents.Cbx = UserForm1.Controls(1)` but I get a "non valid argument" error message, by the way can you give a link to the examples you are talking about ? @Rory – otus Jul 23 '15 at 10:54
I've added some sample code above. There are hundreds of samples you can find with a Google search. – Rory Jul 23 '15 at 10:57

score 0 · Answer 2 · answered Jul 23 '15 at 09:40

I didn't understand much of the question, but this should run (untested)

Sub MakeCombo()

    'Create the combobox
    Set oleCbx = UserForm1.Controls.Add("Forms.ComboBox.1")

    oleCbx.Object.AddItem "1"
    oleCbx.Object.AddItem "2"

    'hookup the events
    Application.OnTime Now, "HookupEvents"

End Sub

Add OLEObjects to a userform

2 Answers2