TextBox real time filter for ListBox

[Boechat]

Greetings everyone,

first of all thanks for all the support you've been offering. Eventhough this is my first post I've already learned a lot as a guest here.

Right now I'm facing the following issue:

I have a userform containing, among other info, a listbox with 4 columns and a textbox which I wish to use as a real time filter (filter as I type) for the list. Since the list is dynamic I can't work with fixed ranges.
Furthermore I would like the search to be case insensitive and to find any field containing the typed info and not only starting letters or such (If I type "an" I should find Canada, and not only Anne, for instance).

Below is the code I have on this form so far, it contains 2 commands to open other forms, the command to fill the list and the command to delete old data. Hope you guys get to help me out!

___________________________________________________________________________________________________ Private Sub UserForm_Activate() Me.ListBox1.List = Sheets("Contatos").Range("A2:D500").Value End Sub ___________________________________________________________________________________________________ Private Sub CommandButton3_Click() Contactsframe.Hide Mainframe.Show End Sub ___________________________________________________________________________________________________ Private Sub CommandButton4_Click() Contactsframe.Hide Newcontactframe.Show End Sub ___________________________________________________________________________________________________ Private Sub CommandButton5_Click() ListBox1.RemoveItem (ListBox1.ListIndex) Range("Cont").Cells(Me.ListBox1.ListIndex + 1, 1).EntireRow.Delete End Sub ___________________________________________________________________________________________________

<tbody>
</tbody>

Thanks again for your support,

Boechat

 

[mikerickson]

Code like this would work

Private Sub TextBox1_Change()
    Dim i As Long
    Dim testString As String
    testString = LCase("*" & TextBox1.Text & "*")
    
    Call FillListBoxWithAll
    With ListBox1
        For i = .ListCount - 1 To 0 Step -1
            If (Not (LCase(.List(i, 0)) Like testString) And (Not (LCase(.List(i, 1)) Like testString))) _
                And (Not (LCase(.List(i, 2)) Like testString) And (Not (LCase(.List(i, 3)) Like testString))) Then
                .RemoveItem i
            End If
        Next i
    End With

End Sub

Sub FillListBoxWithAll()
    ListBox1.List = Range("A1:D10").Value
End Sub

Private Sub UserForm_Initialize()
    Call FillListBoxWithAll
End Sub

I know that you said that you were using a dynamic range. This demo program uses a fixed range, so you will have to change the code in FillListBoxWithAll to match your situation. But no other change needs to be made.

 

[Boechat]

Thank you for the quick and precise reply. It works perfectly!

If we are already at it, what is the command I should use to clear textboxes when I open a form that I have used before.

Let's say I have a "new register" form, I fill it out and save the changes. When I access it again in order to register a new entry, how do I get the text boxes to be empty instead of filled with the previously typed info?

There should be a TextBox1.Clear command if I'm not mistaken.

Thank you once again for your support!!

 

[mikerickson]

There are two ways to dismiss a user form. Unloading and hiding.

If you hide the user form, its still there just hidden. And the control values are the same as when it was hidden.
If you unload the user form, it goes away and the next time you invoke the user form with Userform1.Show, its a new, different user form and the controls' values are their defaults (typically for Textboxes this means empty)

It sounds like you are using Userform1.Hide to dismiss your user form. If, instead you use the line

Unload Userform1

then the text boxes of the new user from will be blank. HOWEVER, you have to save the values from the controls to cells before the Unload line.

Alternatly, if you continue to use .Hide, you will have to specifically clear each control's values

TextBox1.Text = vbNullString
TextBox2.Text = vbNullStriing
' etc.

 

[Boechat]

Can't thank you enough, Mikerikson!

Thread Solved