excel - 未触发 VBA 组合框更改事件

Question

我对 ComboBox 事件处理程序有这个问题。

我设法创建(并填充项目)我想要的组合框，代码似乎工作正常。但是在程序运行后，如果我尝试在其中一个组合框中选择一项常规项目，似乎没有调用 _Change 方法 --> 我无法处理更改事件。

这是我的类模块(类名:“DB_ComboBox”)

    Option Explicit

    Public WithEvents DB_ComboBoxEvents As MSForms.ComboBox
    Private DB_ComboBox_Line As Integer

    Private Sub DB_ComboBoxEvents_Change()
        MsgBox ("Line : " & DB_ComboBox_Line)
        'Here I want handle The comboboxes changes
        'But this routine is not called!

    End Sub

    Sub Box(CBox As MSForms.ComboBox)
        Set DB_ComboBoxEvents = CBox
    End Sub


    Public Property Let Line(value As Integer)
        DB_ComboBox_Line = value
    End Property

    Public Property Get Line() As Integer
        Line = DB_ComboBox_Line
    End Property

这是我的“主模块”，我在其中创建组合框并将它们传递给“DB_ComboBox”的集合

        Sub CreateComboBox(IncCBoxes)

        Dim curCombo As MSForms.ComboBox
        Dim rng As Range
        Dim tot_items As Integer
        Dim incAddItem As Integer
        Dim incAddItemBis As Integer
        Dim itemBaseArray() As String
        Dim TEMP_ComboBoxInst As New DB_ComboBox


        Set rng = ActiveSheet.Range("J" & IncCBoxes)

        Set curCombo = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Object



         'Add the items
        itemBaseArray = Split(Foglio7.Cells(IncCBoxes, DBColFileComboIndexErrori), ";")

        For incAddItem = 0 To UBound(itemBaseArray)

            Dim itemLastArray() As String
            itemLastArray = Split(itemBaseArray(incAddItem), ",")

            For incAddItemBis = 0 To UBound(itemLastArray)
                curCombo.AddItem (itemLastArray(incAddItemBis))
            Next

        Next


        TEMP_ComboBoxInst.Box curCombo
        TEMP_ComboBoxInst.Line = IncCBoxes
        customBoxColl.Add TEMP_ComboBoxInst


    End Sub

谁能告诉我我错过了什么？

非常感谢

Answer 1

这看起来像一个时间问题:
在另一个打开的文件中运行此代码将起作用。在同一个文件中它没有。
将添加到您的类与添加 OLEControl 分开，即:
现在使用 Application.ontime

见下面的代码:

Private customBoxColl As New Collection

Sub CreateComboBox(IncCBoxes As Long)

        Dim curCombo As MSForms.ComboBox
        Dim rng As Range
        Dim tot_items As Integer
        Dim incAddItem As Integer
        Dim incAddItemBis As Integer
        Dim itemBaseArray() As String
        Dim itemLastArray() As String

        Set rng = ActiveSheet.Range("J" & IncCBoxes)

        With ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height)
            Set curCombo = .Object
        End With

         'Add the items
        itemBaseArray = Split(Foglio7.Cells(IncCBoxes, DBColFileComboIndexErrori), ";")
        For incAddItem = 0 To UBound(itemBaseArray)
            itemLastArray = Split(itemBaseArray(incAddItem), ",")
            For incAddItemBis = 0 To UBound(itemLastArray)
               curCombo.AddItem (itemLastArray(incAddItemBis))
            Next
        Next
    Application.OnTime Now, "'CallToClass """ & curCombo.Name & """,""" & IncCBoxes & "'"
End Sub

Sub CalltoClass(ctl As String, myline As Long)
Dim TEMP_ComboBoxInst As New DB_ComboBox
        TEMP_ComboBoxInst.Box ActiveSheet.OLEObjects(ctl).Object
        TEMP_ComboBoxInst.line = myline
        customBoxColl.Add TEMP_ComboBoxInst
End Sub