Macro to find Partial/Exact match from the sheet on the UserForm

  • Hi All,


    Below code helps find the exact match from the sheet2.
    I need help with showing the partial matches as I type in the search box. for example, the item in the sheet 2 is Department ID, as I start typing the keyword "Dep" it starts showing me all the possible matches start with "Depa" which could be Department, Department Name, Department ID, etc. until I finish typing the Department ID to be the exact item that I am looking for.



    I would appreciate your help here.
    Thanks.

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    You can change the look at part xlPart.

    Code
    1. LookAt:=xlWhole


    Code
    1. LookAt:=xlPart


    and probably change the match case to true.


    If you are looking for a word with an uppercase D then typing uppcase D with match case set to true should eliminate a lot of false matches, meaning words that simply have d anywhere in them.


    So maybe this, it's triggered by the worksheet change event which runs every time you type a new character into TextBox1.


  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    Thanks much Skywriter :) It looks good.
    The only thing I am thinking instead of using results to show in textbox 2,3,4,5,6 would it be possible to show list box or any other object that I could use which shows all the possible option while typing?
    E.g. Department ID, As I am typing it shows all the possible results in list box (or other object) as I am typing and ones I am completed typing Department ID, I only see Department ID in listbox as final search. Your suggested code is searching the way I wanted but list box or any other other that might show type as I go results... Any suggestions which object to use and how to incorporate in the code??? Highly appreciated yor help here. thanks

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    skywriter, pls find attachment. The ideal situation would be to show listbox or similar object to show results as I type.


    Sample data,


    [TABLE="width: 117"]

    [tr]


    [td]

    John Cona

    [/td]


    [/tr]


    [tr]


    [td]

    Jack Niel

    [/td]


    [/tr]


    [tr]


    [td]

    Kathy Smith

    [/td]


    [/tr]


    [tr]


    [td]

    John Kellem

    [/td]


    [/tr]


    [tr]


    [td]

    John Ring

    [/td]


    [/tr]


    [/TABLE]


    upon typing "John" list box shows all the possible matches until reaches to specific "John" e.g. "John Kellem" .


    Thanks for your help in advance

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    Supposing you have


    John Smith
    Jon Hughes
    Jill Smith


    Do you want to type J and have it immediately fill and show all three and then type "o" and have Jill disappear from the list and then type "h" and have only John Smith left, or do you want to type something and hit a search button and have it match what it can to what you typed?

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    Exactly, when I type J and have it immediately fill and show all three and then type "o" and have Jill disappear from the list and then type "h" and have only John Smith left.
    Thanks

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    My apologies for the delay, I got a little sidetracked.
    You listbox wasn't a listbox it was a combobox so I changed it.


    Here's all the code and then your workbook is attached.


  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    great work and many thanks :)


    One thing I notice that will be very helpful for me...as I type the word selection the list gets shorter which is exactly the code should do and it is doing...but when I backspace the type selection the list does not gets back with full selection....Is that possible to do that in the code??? e.g, "John S" gives me word in the list John Smith but when I backspace "S" and leave John in the textbox...the listbox does not goes back and give me the full list of all the items in the listbox?

  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    Try this.


  • Re: Macro to find Partial/Exact match from the sheet on the UserForm


    Bruce:
    The code is working like a charm...cant thanks enough :thumbcoo:
    I was thinking that listbox might work good for my need but apparently it is too congested for sentences that I have in my columns....Can we bring excelsheet column A:E and replace the listbox with excel sheet window or subform??? I am sure this is my last and final request on this which will make the code and design looks perfect :) Many thanks in Advance