未触发VBA组合框更改事件

Question

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

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

这是我的类模块(类名："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

Answer 2

我知道这不适用于您的特定问题，但我会在这里为其他可能有此问题的人发布这篇文章。在我的案例中，事件停止触发，因为我刚刚将数据库复制到一个新的Github repo中。

在重新开放Access时，在前一天一切正常的情况下，活动并没有启动，这让我完全困惑，尤其是因为SO的回答似乎都没有解决我的问题。基本上，Access会阻止宏和代码，并要求通过单击屏幕顶部的黄色小警告上的"确定"重新启用它。