Deleting results as User Input Changes

[CrispyAsian]

Hello everyone,

I have a code that I've been working on for a while and I've got myself stuck and can't figure out how to make it work. Right now I have a ComboBox where someone can type in their name or look for it using the drop down. As the user types, the ListBox will Populate with A column cells if the name matches in Column G. It works perfectly for now except as the user continues to type, if the name in the ComboBox no longer matches column G (say for example there's a "Mills" and a "Miller" in column G and as "Mills" starts typing, it would list the column A cells both "Mills" and "Miller" until he got to the "s") the old names that used to match stay there and won't go away. I need it so the list starts clearing out with names that don't match. Here's what I have so far:

Private Sub ComboBox1_Change()

Dim c As Range
Dim name As String
Dim lng1 As Long


If ComboBox1.ListIndex > -1 Or ComboBox1.Value <> "" Then
    FolderSelect.Visible = True
    Sheets("Student Folders").Activate
    name = ComboBox1.Value
[COLOR=#ff0000]    For lng1 = 0 To Me.ListBox1.ListCount - 1[/COLOR]
[COLOR=#ff0000]            If Me.ListBox1.Selected(lng1) Then[/COLOR]
[COLOR=#ff0000]                If InStr(1, Me.ListBox1.List(lng1), name, vbTextCompare) = 0 Then[/COLOR]
[COLOR=#ff0000]                    ListBox1.Clear[/COLOR]
[COLOR=#ff0000]                Else[/COLOR]
[COLOR=#ff0000]                End If[/COLOR]
[COLOR=#ff0000]            End If[/COLOR]
[COLOR=#ff0000]    Next lng1[/COLOR]
[COLOR=#ff0000]    With ListBox1[/COLOR]
        .Visible = True
        For Each c In Sheets("Student Folders").Range("G4:G200")
            If c.Value <> "" Then
                If InStr(1, c.Value, name, vbTextCompare) > 0 Then
                    AddValueListBox1 (Cells(c.Row, 1))
                Else
                End If
            End If
        Next c
    End With
Else
    ListBox1.Clear
    FolderSelect.Visible = False
    ListBox1.Visible = False
End If


End Sub

The red section is the code I put in to try to make this happen but I don't think it actually does anything. Can anybody help?

Thanks.

 

[CrispyAsian]

Sorry,

Just realized I forgot to add the "AddValueListBox1" function. Here's what that looks like:

Private Function AddValueListBox1(str As String)

Dim valExists As Boolean


valExists = False
    
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.List(i) = str Then valExists = True
    Next i
    
    If valExists Then
    Else
        ListBox1.AddItem str
    End If
End Function