TextBox real time filter for ListBox

Boechat

New Member
Joined
Jul 16, 2016
Messages
44
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,773
Code like this would work
Code:
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.
 
Last edited:

Boechat

New Member
Joined
Jul 16, 2016
Messages
44
Thank you for the quick and precise reply. It works perfectly! :biggrin:

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,773
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
Code:
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
Code:
TextBox1.Text = vbNullString
TextBox2.Text = vbNullStriing
' etc.
 

Forum statistics

Threads
1,081,526
Messages
5,359,275
Members
400,523
Latest member
ExcelNewbie98

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top