none
VBA - Filter List Box - From TextBox Search - Run Macro RRS feed

  • Question

  •  

    Hi friends,

     

    I am trying to make my TextBox1 work with my ListBox1 to Filter Search Results.

     

    My set up is a bit strange , but for a good reason.

     

    The Listbox  populates from an excel sheet

     

    Next I am trying to search a TextBox1  - and filter the results.

    So I can search for what I want but I couldn’t find a way to fix that. 

     

    The goal is to try and run a macro stored within the document when an item is selected from the listbox, I can do that if I use listindex=0,1,2,3 etc.

     

    I wanted to try and simplify that by only running a macro when the item is selected.

     

    I have wasted away this Sunday.

     

    How can I fix my text box search - there is something missing.

     

    And run a macro only when the item is selected from the listbox - I have googled forever and still no luck :(

    I have attached the problematic duo in question -  doc and the xl sheet

     

    https://1drv.ms/w/s!AvcJQhgRih3LkmNwEln3y9TLE28w

     

    https://1drv.ms/x/s!AvcJQhgRih3LkmdloYUSmLRG1NCK

     

     

    Thank you for your help  - I really appreciate it


    Cheers Dan :)


    • Edited by Dan_CS Monday, January 23, 2017 3:30 AM
    Monday, January 23, 2017 3:23 AM

Answers

  • Hello Dan_CS,

    I am hoping that I now understand what you are attempting to do. Test the code in the example (xl file and Word file) that I have uploaded to OneDrive.

    I have used family names and given names in the list in the xl worksheet. In particular try the Mac combinations and the more of the name you type the further the filter is applied.

    I have saved the range for the list to an array that has been dimensioned as Public in Module1 and also see that I have added a button to reset the list to for ListBox by using the public array variable.

    https://1drv.ms/u/s!ArAXPS2RpafCg1Qe6aYHflvfWO0K


    Regards, OssieMac

    • Marked as answer by Dan_CS Wednesday, January 25, 2017 2:58 AM
    Wednesday, January 25, 2017 2:24 AM

All replies

  • Not sure that I am interpreting your question correctly but try the following code and see if it is what you want.

    Note all of my comments.

    'Private Sub TextBox1_Change()  'Change event fires as soon as a character is typed
    Private Sub TextBox1_AfterUpdate()  'AfterUpdate waits until move off the control (ie. fully updated)
        Dim i               As Long
        Dim n               As Long
        Dim Str             As String
        Dim sTemp           As String
       
       'Equals is always case sensitive
        'Remove LCase if you want it to be case sensitive
        Str = LCase(Me.TextBox1.Text)
       
        n = Me.ListBox1.ListCount
       
        For i = n - 1 To 0 Step -1 'Work backwards when deleting items
            'Equals is always case sensitive
            'Remove LCase if you want it to be case sensitive
            sTemp = LCase(Me.ListBox1.List(i))
           
            If InStr(sTemp, Str) > 0 Then
                ListBox1.RemoveItem (i)
                'Exit Sub   'Uncomment to Exit if value found
            End If
           
        Next i
       
    End Sub


    Regards, OssieMac

    Monday, January 23, 2017 5:26 AM
  •  

    Hello Ossie,

     

    Thank you for helping me with textbox search.

     

    The listbox gets populated from excel then..

     

    I am trying to emulate a search as you type so it filters the listbox items.

     

     

    I have put the code in and for some reason its not filtering the Listbox items.

     

    I'm not sure if  a wildcard * is needed.

     

     I know this functionality exists but I can't seem to find what is needed to filter the data.

    I will keep testing


    Cheers Dan :)

    Monday, January 23, 2017 2:13 PM
  • I previously completely misunderstood the question.

    If instead of typing into a textbox, ensure that the list in the ListBox is sorted and MatchEntry property set to 1 - fmMatchEntryComplete then select the ListBox and start typing and it automatically selects the match.


    Regards, OssieMac

    Monday, January 23, 2017 9:57 PM
  • Hello Ossie,

     

     

    I have been trying to get the listbox and text box to search the whole day

    I don’t know why it is playing up.

     

    It basically filters all the wrong things and then deletes them all, so the data cant be refreshed to original.

     

    I changed to the suggested but still no joy :(


    Cheers Dan :)

    Monday, January 23, 2017 10:15 PM
  • With my testing with a sorted list and the MatchEntryComplete then instead of using the TextBox, I just start typing into the listbox and it selects the first match to first letter and as I continue typing it gets to a better match so why is it necessary to remove further filter the list? Note if excessive time between typing letters then loses the initial match. Not sure exactly what this time delay is but it is there.

    Regards, OssieMac

    Monday, January 23, 2017 10:22 PM


  • Hello Ossie,

     

    Pardon my  impetuous ignorance.

     

    After a whole day 

     

    I have found something that is similar to what I am trying to achieve.

     

    I meant to explain that I was trying to filter the results so I get all the values with apple in them.

     

    I will put in apple in the TextBox1

     

     

    Private Sub UserForm_Initialize() With Me.ListBox1 .AddItem "Apple" .AddItem "Apple" .AddItem "Apple" .AddItem "Apple" .AddItem "Bob" .AddItem "Bill" .AddItem "Mark" .AddItem "Apple" .AddItem "Apple" .AddItem "Bob" .AddItem "Bill" .AddItem "Mark" .AddItem "Apple" .AddItem "Apple" .AddItem "Bob" .AddItem "Bill" .AddItem "Mark" End With End Sub

    ' How can I get the Listbox to display and filter the results

    when I enter a search term into the TextBox1

    Private Sub TextBox1_change() Dim strFilter As String Dim lngIndex As Long strFilter = Me.TextBox1.Text For lngIndex = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.List(lngIndex, 2) <> strFilter Then ListBox1.RemoveItem (lngIndex) End If Next End Sub


    https://www.experts-exchange.com/questions/28960114/Excel-VBA-Real-Time-Search-or-Filtering-in-Listbox.html

     I have gone back to basics as suddenly it has catapulted me into listbox land for which I had no idea it was this complex - am i able to simply filter a list box with a search keyword that i put in the text box?

    For Example Can I display all the results that have apple in them? in the listbox, I can then make a choice from the various apples.


    Cheers Dan :)




    • Edited by Dan_CS Tuesday, January 24, 2017 11:52 PM
    Tuesday, January 24, 2017 11:44 PM
  • Hello Dan_CS,

    I am hoping that I now understand what you are attempting to do. Test the code in the example (xl file and Word file) that I have uploaded to OneDrive.

    I have used family names and given names in the list in the xl worksheet. In particular try the Mac combinations and the more of the name you type the further the filter is applied.

    I have saved the range for the list to an array that has been dimensioned as Public in Module1 and also see that I have added a button to reset the list to for ListBox by using the public array variable.

    https://1drv.ms/u/s!ArAXPS2RpafCg1Qe6aYHflvfWO0K


    Regards, OssieMac

    • Marked as answer by Dan_CS Wednesday, January 25, 2017 2:58 AM
    Wednesday, January 25, 2017 2:24 AM
  • Hello Ossie,

     

    May I say thank you so much for your incredible help!

     

    I have been working on this for weeks on and off, becuase there were so many problems connecting word to excel  - and im none the wiser.

    The problem is I am using word to populate a listbox from excel - so by its very nature it became messy.

    Everything I found on listboxes was attached to excel within Excel so that made my newbie job very very hard to begin with :(

     

    But im very stubborn and once I see something that would help me do a job, I just cant give up on my idea. I have always wanted a listbox in word but I could never make it work :( and filtering was out of the question. But I had an urgent outlook list I needed to put in the excel spreadsheet but I was also using word and this cross app inteference was really amplified yesterday.

     

    I can now use this for a multitude of jobs, oh as well as list some macros to in the listbox.  Also I just didn’t have the energy to look for a combobox solution as if that would be any better so alas  I went back to basics.

     

    But you have solved it now!!!

     

    Yay I am sooo happy

     

    So thank you for your very kind and incredbily awesome help .

    Reaally stoked to bits!

     

    Have a Wonderful week now - Ossie ( if that’s your name)

    I have one shiny new toy the ListBox to set up tomorrow - with lots of lists of names and things and things etc

    :)


    Cheers Dan :)

    Wednesday, January 25, 2017 2:58 AM
  • Hi Ossie - I was hoping you could make that code available again.  It turns out that I am trying to achieve the exact same thing!

    I do have a question, though.  Is it possible to have a single search box for a multicolumn listbox?  That is what I would like to do.

    Thanks!

    LS

    Tuesday, January 31, 2017 6:18 PM
  • To LS,

    File restored to following link. Download the file to your computer.

    https://1drv.ms/u/s!ArAXPS2RpafCg1Qe6aYHflvfWO0K

    It is a zip file containing 2 files. One is an Excel file and the other a word file that contains the Userform and ListBox. Open the word file and the ListBox is populated from a table in the Excel file.  Basically the same code can be used in just say an Excel file.

    The ListBox is a multi column but the search filter is only applied to the first column. You will see that it has names with family name in first column and given name in second column and as you type the family name the list box is filtered on the characters that have been typed in the TextBox. In particular. try the Mac names and you will see the progressive filter being applied.

    If you have further questions regarding this then please start a new thread.


    Regards, OssieMac

    Tuesday, January 31, 2017 9:55 PM
  • Thanks Ossie, but I still seem to be getting a message stating that the file is not there.  Can you please check one more time?

    Sorry for the inconvenience.  i appreciate the help!

    LS

    Tuesday, January 31, 2017 11:04 PM
  • I was able to go to it with the link so don't know what the problem is. I had the same problem a few days ago trying to get a file that someone else uploaded for me and I assumed that they had not got the share link properly but now I am thinking that was possibly not the problem.

    Anyway I deleted the file and uploaded it again and got a new link as follows so see how you go now.

    https://1drv.ms/u/s!ArAXPS2RpafCg1QCoH9V-IFODRHU


    Regards, OssieMac

    Tuesday, January 31, 2017 11:26 PM
  • Got it!  Thanks!
    Tuesday, January 31, 2017 11:39 PM
  • Hi Ossie - I was hoping you could make that code available again.  It turns out that I am trying to achieve the exact same thing!

    I do have a question, though.  Is it possible to have a single search box for a multicolumn listbox?  That is what I would like to do.

    Thanks!

    LS

    I'am also trying to achieve the same thing!

    Would it be possible to have the code available again?

    Thanks!

    Friday, August 17, 2018 8:46 AM
  • Hi Ossie, 

    Please could you share the file/ code with me im trying to do the same thing.

    Thank you, Kind Regards

    Monday, December 10, 2018 12:08 PM