excel - (用户窗体)ListBox 列表属性由代码更新时未触发 ListBox 更改事件

Question

我有一个带有多列列表框和组合框的用户窗体。 ListBox 默认显示完整的数据集。 ComboBox 包含来自 ListBox 中某一列的值。从 ComboBox 中选择一个值会过滤 ListBox 数据。

当 ListBox 发生这种变化时，我想 Debug.Print "A message..."但没有成功。

我没有收到错误消息。调试器甚至不会单步执行 ListBox_Change 事件过程。我在事件过程中只有上面的 Debug.Print... 行。

非常感谢您对解决方案/线索的帮助。

问题更新:代码如下:

Dim arrAllData() As Variant
Private Sub UserForm_Initialize()

Call CentreForm(Me)

arrAllData = Range("tblData") 'Excel table of 11 columns

Me.lbxData.List = arrAllData

Set collProjName = UniqueItemsFromRanger(Range("tblData").Columns(2))

For i = 1 To collProjName.Count
    Me.cboProjName.AddItem collProjName(i)
Next i

End Sub

Private Sub cboProjName_Change()
Dim NewList() As Variant, NewListSingleRow(0 To 0, 0 To 10) As Variant
Dim colNbr As Integer

Erase NewList
If Me.cboProjName.Value <> "" Then
    With Me.lbxData
        NewList = .List

        NewList = FilterData(NewList, Me.cboProjName.Value, 2)
        If UBound(NewList, 2) > 0 Then
            .List = Application.Transpose(NewList)
        Else
            For i = 0 To UBound(NewList, 1)
                NewListSingleRow(0, i) = NewList(i, 0)
                .List = NewListSingleRow
            Next i
        End If
    End With
End If
End Sub

Private Sub lbxData_Change()
    Debug.Print "Test Message..."
End Sub

Function UniqueItemsFromRanger(Rng As Range) As Collection
Dim coll As New Collection, i As Long

On Error Resume Next
For i = 1 To Rng.Rows.Count
    coll.Add Item:=Rng.Cells(i, 1), Key:=CStr(Rng.Cells(i, 1))
Next i

Set UniqueItemsFromRanger = coll
End Function

Function FilterData(arrData() As Variant, FilterFor As String, ColumnToFilter 
As Long) As Variant
Dim arrDataFiltered() As Variant
Dim rowCount As Long, colCount As Long, filteredCount As Long
rowCount = UBound(arrData, 1)
colCount = UBound(arrData, 2)

filteredCount = 0

For i = 0 To rowCount
    If arrData(i, ColumnToFilter - 1) = FilterFor Then
        ReDim Preserve arrDataFiltered(0 To colCount, 0 To filteredCount)
        For j = 0 To colCount
            arrDataFiltered(j, filteredCount) = arrData(i, j)
        Next j
        filteredCount = filteredCount + 1
    End If
Next i

FilterData = arrDataFiltered

End Function

Answer 1

When this change to the ListBox happens, I want to Debug.Print "A message..." but with no success.

原因
ListBox_Change事件处理程序仅在行选择更改(单击列表框中已选择或未选择的任何行)和 时执行不是 当列表框被清除或添加项目时。

备选

捕获由过滤器引起的“变化”的几种方法

使用Debug.Print (正如@MathieuGuindon 和@Kevinhiew 建议的那样)/Msgbox/Update a Label/Update Excel Statusbar等等...在Combobox_Click事件。

检查前后Listbox.Listcount .这有一个基本缺陷，如果项目更改但列表计数没有，则不会发出警报。你可以用 filteredCount 来反驳。在 Function FilterData()检查数组是否已更改。

填充列表框后，使用 If ListBox1.ListCount > 0 Then ListBox1.ListIndex = 0 选择第一项