search using textbox and display on list box in userform

  • I have a user form with a listbox, textbox, and command button. The intent is for a user to type into the textbox, click the button, and the code would search through the database for the text. database consist of 5 column like below;
    1. client
    2. province
    3.district
    4.commune
    5. village


    im trying to search for the client name example "sorn" or "sorn tye" than it will display all the related information regarding that name in a listbox. for now, the listbox will display all the info in the database not the one that i search in the textbox.


    Below is some code I've been trying to change to work. Not sure if this is a step in the right direction.


  • Re: search using textbox and display on list box in userform


    It would help greatly if you could attach a workbook, complete with your user form.
    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: search using textbox and display on list box in userform


    Is this what you are trying to do?


    I changed the dynamic named range ("DataList") you were using. It will now cover just the data in your database and exclude the headers plus many empty rows below the table which the previous named range included.


    There is no need to have a command button on UserForm2, this is the code will load the ListBox with filtered data from your database for any change to the text in the TextBox.


    I have used Labels for the ListBox column headers (placed immediately above the ListBox) instead of using the ListBox ColumnHeader Property. This is much easier when the ListBox needs to be dynamic.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: search using textbox and display on list box in userform


    Much simpler to use AutoFilter, it is also not clear which column is to be searched. This is taken from my form and works in the example workbook with some changes to the column references.



    In more complex versions I would automate AdvancedFilter

  • Re: search using textbox and display on list box in userform


    yeah i worked perfectly like what i want. but can you explain to me why did you changed the dynamic range "DataList" again. im still new here and im still learning. thank you. anyway sorry for the late reply

  • Re: search using textbox and display on list box in userform


    I changed the "DataList" dynamic named range because the formula you had used included the header row and also extended down many empty rows below the database. The change I made means the header row is not included and only rows containing data are referenced.


    Yes the full database will be loaded into the List Box when the User Form is opened.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: search using textbox and display on list box in userform


    Does your database in you actual file still start in column B with Row 3 being the header row and the data starting in Cell B4?


    Did you copy the formula for the dynamic named range ("DataList") correctly, is that named range still named "DataList"?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: search using textbox and display on list box in userform


    no, it start with column D with row 1 being the header row and data starting in cell D2.


    and yes, i have changed my dynamic named range to ("myData")



  • Re: search using textbox and display on list box in userform


    The changes you made to the code were correct.


    I suspect that you got the formula wrong for the dynamic Named Range "myData". With your data table starting in Cell D1 and Row 1 being the header row the formula should be


    =OFFSET(Sheet1!$D$2,,,COUNTA(Sheet1!$D:$D)-1,5)

    See the attached file for working example.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: search using textbox and display on list box in userform


    Quote from blxck;781883

    no, it start with column D with row 1 being the header row and data starting in cell D2.


    and yes, i have changed my dynamic named range to ("myData")




    Great it's work for me :thumbcoo: how to edit this code when i choose optionbutton 1 to select this code or optionbutton 2 to select another code

  • Re: search using textbox and display on list box in userform


    Do you mean option buttons on a user form or on a worksheet?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.