(UserForm) ListBox Change event is not firing when ListBox List property is updated by code

Question

I have a userform with a multi-column ListBox and a ComboBox. The ListBox by default shows a full set of data. The ComboBox contains values from one of the columns in the ListBox. Selection of a value from the ComboBox filters the ListBox data.

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

I get no error messages. The debugger doesn't even step through the ListBox_Change event procedure. I only have the above Debug.Print... line in the event procedure.

Your assistance for solutions/clues will be much appreciated.

Question updated: Code shown below:

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

Hello. Please post the code you have tried. I suggest reading Minimal Reprodudible Example and How to Ask. — Zack E
ListBox1_Change fires only when an item is selected in the listbox. Not when LB is cleared or an item is added. — Siddharth Rout
You don't need to handle a Change event if you're the one changing the listbox... put your Debug.Print in the code that performs the filtering. — Mathieu Guindon
I would get rid of On Error Resume Next until you know the errors that appear (if any). I would follow @MathieuGuindon's advice on this one. — Zack E
FWIW Erase NewList is redundant, the array is local and already uninitialized... — Mathieu Guindon

Siddharth Rout Siddharth Rout · Accepted Answer · 2019-08-16T15:20:49

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

Reason

The ListBox_Change event handler is executed only when the row selection changes (Any row whether selected or unselected in the ListBox is clicked) and NOT when the Listbox is cleared or an item is added.

Alternative

Couple of ways to capture the "change" caused by filter

Use Debug.Print (as @MathieuGuindon and @Kevinhiew suggested) /Msgbox/Update a Label/Update Excel Statusbar etc... in the Combobox_Click event.
Check the before and after Listbox.Listcount. This has a basic flaw that if the items changed but the list count didn't then this will not alert. You could counter that with the filteredCount in Function FilterData() to check if the array was changed.
After populating the listbox, select the first item using If ListBox1.ListCount > 0 Then ListBox1.ListIndex = 0

(UserForm) ListBox Change event is not firing when ListBox List property is updated by code

2 Answers