When _Change() dynamically clears and re-adds items to ComboBox, box remains null when selecting item

[T1E1]

I'm working on a search form for an Excel-based application. I'm using ComboBoxes where there will be up to 1000 entries in the list. I would like it to operate such that, when the user types in characters into the ComboBox, the box's list is automatically modified to show only those entries that match the characters entered. I have that much working with no problems, and the drop-down list changes as it should. However...

When the user directly selects any of the items in the list through the drop-down, either before or after typing in some characters, the .value property remains null and the box empty. It seems to be a problem with the box being re-entrant and the .index value changing, but I've been fighting this thing for a couple of days now and haven't gotten anywhere. Any ideas?

I've distilled the problem down to the code below. To replicate the problem:

• Create a UserForm
• Drag a ComboBox onto the new form
• Rename the ComboBox to cbxData
• Paste the code below into the Code window
• Try to select one of the drop-down items, either before or after typing in characters to filter (e.g. a, p, b, *a, *w, etc)

Thanks in advance.

Excel 2019
Windows

Dim aData As Variant

Private Sub cbxData_Change()

    'On entry, all items from array aData appear in the dropdown box.
    'As you enter letters into the combobox, only the items from aData which start with those letters will remain, and all others will be removed from the combobox list.
   
    'Prefacing the data entry with an asterisk (*) will filter for all items in aData which have that sequence of characters after the * somewhere in the string (wildcard search).
   
    'However, selecting any item from the drop-down box will result in cbxData.Value being null, rather than the item showing up in the box.
   
    Dim bWildcardSearch As Boolean
    Dim sSearchString As String
    Dim i As Integer
   
    Debug.Print "Me.cbxData.Value on entry = " & cbxData.Value
    Debug.Print "Me.cbxData.ListIndex on entry = " & CStr(Me.cbxData.ListIndex)
   
    If Left(Me.cbxData.Value, 1) = "*" Then
        bWildcardSearch = True
        sSearchString = Mid(Me.cbxData.Value, 2)
    Else
        bWildcardSearch = False
        sSearchString = Me.cbxData.Value
    End If

    Me.cbxData.Clear
    For i = 0 To UBound(aData)
        If bWildcardSearch Then
            If InStr(UCase(aData(i)), UCase(sSearchString)) > 0 Then Me.cbxData.AddItem aData(i)
        Else
            If UCase(Left(aData(i), Len(sSearchString))) = UCase(sSearchString) Then Me.cbxData.AddItem aData(i)
        End If
    Next i
   
    Debug.Print "Me.cbxData.Value on exit = " & cbxData.Value
    Debug.Print "Me.cbxData.ListIndex on exit = " & CStr(Me.cbxData.ListIndex)
   
End Sub

Private Sub UserForm_Initialize()
   
    'cbxData Properties are left at default in the form editor and set below:
    Me.cbxData.MatchEntry = fmMatchEntryNone
   
    aData = Array("Apples", "Oranges", "Peaches", "Bananas", "Kiwi")
    Me.cbxData.List = aData
   
End Sub

 

[RoryA]

You might find the KeyUp event works better here as long as you make sure the keys are not things like arrow keys and tab keys. You might also want to use Filter for wildcard searches rather than looping through all the items.

 

[T1E1]

RoryA, thank you very much! That worked very well. By moving the code into that event, the box updates as it should, and the _Change() event still catches the update value.

I haven't used the Filter feature yet. I'll have to research that. Any quick pointers regarding that? I've used VBScript for many years, but this is my first large-scale Windows-driven VBA app, so I'm still very much in learning mode.

 

[RoryA]

Given your example list, this would assign an array of the values in the list containing the letter a:

adata = Array("Apples", "Oranges", "Peaches", "Bananas", "Kiwi")
Dim v
v = Filter(adata, "a")

Even for the non-wildcard search, it's probably quicker to filter the array for items containing the text, then iterate that array to just get the ones starting with the search value.

 

[T1E1]

Well, well. Learn something new every day. If my quick read was correct, it would only work for the Else portion of the If statement (i.e. it won't do the InStr wildcard search), but I'll take what I can get and speed up/simplify what I can.

Thanks for the info!

 

[RoryA]

No, it will filter for anything that contains the search text. It is case-sensitive by default, so if you don't want that, you would use:

v = Filter(adata, "a", True, vbtextcompare)

which would return an array containing everything from the source array except Kiwi since that does not have an 'a' in it.

 

[T1E1]

Hmmm.... I see. Yes, food for thought. Something to play with. Useful!
Thanks!

 

[T1E1]

For the sake of those who find this in future years and would like to know what the 'fixed' code looks like, below is the working code, with the Filter() optimization.

If Rory or anyone else feels there are further opportunities for optimization, I would be interested.

Btw, I found the (Not Not aTemp) <> 0 trick on another website, but I'm new here and am not sure what the rules on linking to other sites are, so I'll leave that off. The code works, though I can't say as I truly understand why.

Option Explicit
Dim aData As Variant


Private Sub cbxData_Change()
    Debug.Print "Value changed to " & Me.cbxData.Value
End Sub

Private Sub cbxData_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    'On entry, all items from array aData appear in the dropdown box.
    'As you enter letters into the combobox, only the items from aData
    'which start with those letters will remain, and all others will be
    'removed from the combobox list.
    
    'Prefacing the data entry with an asterisk (*) will filter for all
    'items in aData which have that sequence of characters after the *
    'somewhere in the string.
    
    Dim bWildcardSearch As Boolean
    Dim sSearchString As String
    Dim i As Integer
    Dim iMax As Integer
    Dim aTemp() As Variant
    
    Debug.Print "Me.cbxData.Value on entry = " & cbxData.Value
    Debug.Print "Me.cbxData.ListIndex on entry = " & CStr(Me.cbxData.ListIndex)
    
    If Left(Me.cbxData.Value, 1) = "*" Then
        bWildcardSearch = True
        sSearchString = Mid(Me.cbxData.Value, 2)
    Else
        bWildcardSearch = False
        sSearchString = Me.cbxData.Value
    End If

    'Use Filter() to perform a wildcard (InStr) search
    Me.cbxData.List = Filter(aData, sSearchString, True, vbTextCompare)
    
    'If user doesn't want a wildcard search, then use a temp array to remove any items from
    'the ComboBox that don't start with the search string, and move temp array to ComboBox
    If Not bWildcardSearch Then
        iMax = 0
        For i = 0 To Me.cbxData.ListCount - 1
            If UCase(Left(Me.cbxData.List(i), Len(sSearchString))) = UCase(sSearchString) Then
                ReDim Preserve aTemp(iMax)
                aTemp(iMax) = Me.cbxData.List(i)
                iMax = iMax + 1
            End If
        Next i
        
        Me.cbxData.Clear
        'The line below updates cbxData.List only if the dynamic array has been updated.
        'Without this line, Invalid Property Value errors occur if the revised search yields no
        'results, and the array does not have any elements assigned
        If (Not Not aTemp) <> 0 Then Me.cbxData.List = aTemp
        
        'Clean up after ourselves (optional)
        Erase aTemp
    End If
    
    Debug.Print "Me.cbxData.Value on exit = " & cbxData.Value
    Debug.Print "Me.cbxData.ListIndex on exit = " & CStr(Me.cbxData.ListIndex)
    
End Sub

Private Sub UserForm_Initialize()
    
    'cbxData Properties are left at default in the form editor and set below:
    Me.cbxData.MatchEntry = fmMatchEntryNone
    
    aData = Array("Apples", "Apricots", "Oranges", "Peaches", "Plums", "Bananas", "Kiwi")
    Me.cbxData.List = aData
    
End Sub

 

[T1E1]

Btw, I found the (Not Not aTemp) <> 0 trick on another website, but I'm new here and am not sure what the rules on linking to other sites are, so I'll leave that off. The code works, though I can't say as I truly understand why.

If I had to make a guess, I'd say that the goal is evaluate aTemp logically (True if the array is allocated, false if not), and the Not Not is forcing that logical evaluation without having an inverse result from what you'd expect. I's also guess that the <> 0 could be replaced with = True, or just left off. I tried leaving it off (as in If (Not Not aTemp) Then ...) and it worked.