VBA code to filter listbox as you type in a textbox

[BungleNZ]

Hi,

I have a list of customers in listbox1 (the text values of which come from a spreadsheet), that I want to refine as I type in textbox1. I'm not sure if it's possilbe, but I would like it to work similar to the itunes search (if you're familiar with it) where it searchs for any occurance of the text within the list as opposed to just searching for the letters at the beginning of the word.

Thanks in advance to anyone who can help.

 

[coxycoxycoxy]

Hi blade hunter

Not sure how to linn you in. Sorry so I below are the full address of both
Posts. On my search function I don't want to use list boxes I want it
To filter the actual work sheet.

http://www.mrexcel.com/forum/showthread.php?t=355626&highlight=Filter+itunes

The fourth post in this thread:

http://www.mrexcel.com/forum/showthread.php?t=351768

Will do exactly what you want.

Cheers

Dan

Edit: It searches from the left, its an easy change to make it seach for any occurence:

Hang 5 I will update it for you


OK, Download the sheets, edit the code on the form and change

    If UCase(Left(Sheets("Names").Range("A" & X).Value, Len(UserFilter))) = UCase(UserFilter) Then

To

    If InStr(1, UCase(Sheets("Names").Range("A" & X).Value), UCase(UserFilter)) > 0 Then

 

[Blade Hunter]

Hi Mate, I don't think that this can be done (Which is why I used a form). Excel doesn't trigger an event until you actually confirm a change to a cell using enter, You can't detect a character change.

If you don't want a form, you could make a text box the size of the cell to make it "look" like it is the cell. Have a selection.change event detect if the user selects that cell and select the textbox instead so it would look and act like the cell but wouldn't actually be the cell.

As a simple example I create an ActiveX textbox called NewA1 and used this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then ActiveSheet.NewA1.Activate
End Sub

You can then use this event in a similar way to the old code I posted:

Private Sub NewA1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'This event is triggered on ever key press
End Sub

That should be enough to get you going but let me know if you have any trouble and I will help you out.

Cheers

 

[coxycoxycoxy]

Hi Blade Hunter

Hope you had a nice weekend. Sorry for delay in posting back, just been so busy.

I have set up a test Workbook with two sheets ( Names & Filtered names)and managed to get the original code to work fine. i have also set up the "NewA1" textbox on the sheet called "Names" and that works as you said it would. ( as as far its apears like you are writing in the cell A1)

however i am not sure where to insert the new vba in the old code, so that it filters the actual names sheet as i type in the textbox.

i have posted the three bits of code that i have.

Private Sub UserFilter_Change()
Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Y = 0
For X = 1 To Sheets("Names").Range("A" & Rows.Count).End(xlUp).Row
    If InStr(1, UCase(Sheets("Names").Range("A" & X).Value), UCase(UserFilter)) > 0 Then
        ReDim Preserve MyList(Y)
        MyList(Y) = Sheets("Names").Range("A" & X).Text
        Y = Y + 1
    End If
Next
FilterNames.FilteredList.List = MyList
End Sub

Private Sub NewA1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'This event is triggered on every key press
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then ActiveSheet.NewA1.Activate
End Sub

I was hoping that you would be able to adjust the code for me. just to confirm i need to be able to input the search criteria in cell A1 and filter the data in column A.

Best Regards

Mark

 

[Blade Hunter]

Hi Blade Hunter

Hope you had a nice weekend. Sorry for delay in posting back, just been so busy.

I have set up a test Workbook with two sheets ( Names & Filtered names)and managed to get the original code to work fine. i have also set up the "NewA1" textbox on the sheet called "Names" and that works as you said it would. ( as as far its apears like you are writing in the cell A1)

however i am not sure where to insert the new vba in the old code, so that it filters the actual names sheet as i type in the textbox.

i have posted the three bits of code that i have.

Private Sub UserFilter_Change()
Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Y = 0
For X = 1 To Sheets("Names").Range("A" & Rows.Count).End(xlUp).Row
    If InStr(1, UCase(Sheets("Names").Range("A" & X).Value), UCase(UserFilter)) > 0 Then
        ReDim Preserve MyList(Y)
        MyList(Y) = Sheets("Names").Range("A" & X).Text
        Y = Y + 1
    End If
Next
FilterNames.FilteredList.List = MyList
End Sub

Private Sub NewA1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'This event is triggered on every key press
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$1" Then ActiveSheet.NewA1.Activate
End Sub

I was hoping that you would be able to adjust the code for me. just to confirm i need to be able to input the search criteria in cell A1 and filter the data in column A.

Best Regards

Mark

Just sent you a PM. email me your sheet and I will take a look for you. Shouldn't take too long .

Cheers

Dan

 

[wonder10]

I am trying to implement this "search as you type method" but I keep getting the same Error "Object required" regarding the "FilterNames.FilteredList" part. Is it because you have to create a list with this name beforehand?

Regards

 

[Blade Hunter]

I am trying to implement this "search as you type method" but I keep getting the same Error "Object required" regarding the "FilterNames.FilteredList" part. Is it because you have to create a list with this name beforehand?

Regards

Post your code if you can and I will take a look.

Cheers

 

[wonder10]

Private Sub TextBox1_Change()
Dim ListBox1, Entry
FilterNames.FilteredList.Clear
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each Entry In ListBox1
If InStr(1, Entry, TextBox1.Value, 1) > 0 Then .Item(Entry) = Entry
Next
If .Count > 0 Then FilterNames.FilteredList.List = .keys
End With
End Sub

Private Sub UserForm_Activate()
Dim Entry
FilterNames.FilteredList.Clear
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each Entry In ListBox1
.Item(Entry) = Entry
Next
If .Count > 0 Then FilterNames.FilteredList.List = .keys
End With
End Sub



First an error appears regarding to the variable "Entry" but if I replace it by "i" it's ok... However, as I said in the post before, the problem is I can't get through the "FilterNames.FilteredList" part. Please note that the listbox is already filled so I replaced the code just by "ListBox1"

 

[CurtisK]

interested in how this works out for you. I have a customer list on sheet (1) that I would like to search and display on sheet(2). I'm not sure it will work the way I would like it. As the listbox displays the results of the filtered results I would like to be able to tab to it and enter on the right customer in the listbox and display in a new cell.

 

[wazimu13]

hi Jindon, sorry to revive this but the code here has helped me, so thank you. However i wonder if you can assist please.

I need your dictionary code to work on a listbox that has 3 columns. Basically the worksheet has 3 columns of data and i need this loaded onto the listbox as you type. It works perfectly for one column but i have no idea about dictionaries and would be grateful if you could help. thank you