error on assigning an event to multiple objects with excel vba

Question

I followed all the advice I found here to automatically change the "change" function of comboboxes. All works fine, the code does not produce any error, until I want to change the combobox value on the excel sheet : the macro is not launched.

My code is :

In the class file (class : COptions)

Option Explicit

    Public WithEvents lOptions As MSForms.ComboBox

    Private Sub lOptions_Change()
    MsgBox "hello "
    End Sub

In the module file

Sub macrotest()

Dim j As String
Dim tObject
Set tObject = Sheets("test").OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=False, _
                DisplayAsIcon:=False, _
                Left:=50, Top:=80, _
                Width:=100, _
                Height:=15)
            tObject.Name = "Combobox32"
            tObject.Object.Font.Size = 8
            tObject.Object.BackColor = vbWhite
            tObject.Object.AddItem "blub1"
            tObject.Object.AddItem "blub2"
            'MsgBox "tObject " & tObject.Name

Dim Obj As OLEObject
Dim Cl As COptions
    Set Cl = Nothing
    Set Collect = New Collection '(previously declared as global variable)

    For Each Obj In Sheets("test").OLEObjects
        If TypeOf Obj.Object Is MSForms.ComboBox Then
            MsgBox Obj.Name 'check if we enter in the loop : always successfull
            Set Cl = New COptions
            Set Cl.lOptions = Obj.Object
            Collect.Add Cl
                    End If
    Next Obj
    MsgBox "Collect  " & Collect.Count ' which result is 1

End Sub

So everything works fine, but back on the Excel worksheet, when I change the combobox value, nothing happens.

By the way, I don't really understand the way this code works.

The new object of the defined class (here : Cl) is created and modified. But the combobox, which is the target of the change, where is it modified? Why is it that by changing the new object Cl, it is the target object ComboBox that it is supposed to change ?

I have no idea of what is happening as I did not find this error on the web.

What should I do to fix this problem?

Dick Kusleika · Accepted Answer · 2013-10-19 14:08:20Z

There's a problem with creating OLEObjects and hooking them up to events. Unfortunately, you have to let the procedure that creates the object terminate, then run a procedure that hooks up the event. You can use Application.OnTime to run the 'hook up' procedure right after the 'create' procedure. Like this.

Public Collect As Collection

Sub macrotest()

Dim j As String
Dim tObject
Set tObject = Sheets("test").OLEObjects.Add(ClassType:="Forms.ComboBox.1", _
                Link:=False, _
                DisplayAsIcon:=False, _
                Left:=50, Top:=80, _
                Width:=100, _
                Height:=15)
            tObject.Name = "Combobox32"
            tObject.Object.Font.Size = 8
            tObject.Object.BackColor = vbWhite
            tObject.Object.AddItem "blub1"
            tObject.Object.AddItem "blub2"
            'MsgBox "tObject " & tObject.Name

    Application.OnTime Now, "LinkupCombos"

End Sub

Sub LinkupCombos()

Dim Obj As OLEObject
Dim Cl As COptions
    Set Cl = Nothing
    Set Collect = New Collection '(previously declared as global variable)

    For Each Obj In Sheets("test").OLEObjects
        If TypeOf Obj.Object Is MSForms.ComboBox Then
            MsgBox Obj.Name 'check if we enter in the loop : always successfull
            Set Cl = New COptions
            Set Cl.lOptions = Obj.Object
            Collect.Add Cl
                    End If
    Next Obj
    MsgBox "Collect  " & Collect.Count ' which result is 1

End Sub

Collectives™ on Stack Overflow

error on assigning an event to multiple objects with excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related