+ Reply to Thread
Results 1 to 15 of 15

Userform filter with criteria (Advanced Filter)

  1. #1
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Userform filter with criteria (Advanced Filter)

    I have a Table with building suplies.
    I would like to try and make a userform so I can type some key words in the first 3 listboxes in the userform to find a produkt.

    I am not sure how to go about this and what the best way is.
    In time there could be up to 5000 different types of supplies, so just scroling thrue is not an option.

    I have uploaded a sample file with a userform and the table with the data.

    I do hope some one can guide me in the right direction.

    Any help is much appreciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Userform filter with criteria (Advanced Filter)

    Hello nordicdust,

    Just a quick question, how do you expect the userform to deal with duplicates of the three search value columns, a quick de-dupe showed a minimum of some 46 duplicates of these fields.

    a very fast non considered response is that you appear to need to add a command button into the userform and add the "search code" behind that button (maybe using filters on the three columns entered in the user form

    I haven't actually tried to do this just yet, will take a look a little later in the day.

    Cheers

    Jmac1947

    1. Please consider clicking on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved

  3. #3
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform filter with criteria (Advanced Filter)

    As the word says LISTbox this only produces a list based on criteria. You cannot type in them.

    You will need to switch to either Combobox or TextBox.

    Also what exactly are you trying to achieve ? Do you want to search on each Column seperately (i.e. look for a certain supplier or look for all products with a certain keyword in them) or a combination of columns or ....
    Last edited by bakerman2; 04-01-2019 at 12:47 AM.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Userform filter with criteria (Advanced Filter)

    Hi bakerman2,

    It always pays to go to the basics, sadly I leapt straight into solution mode without even considering the structure of the displayed fields on the userform... sigh...

    Changing to text boxes seems like the option but your question (and by implication mine) remains - exactly what behavior is expected when the fields are completed, are all three needed, multiples etc

  5. #5
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    Hi jmac1947 and @bakerman2

    First, I am fairly new to vba, worked with it for a year now, but just in my spare time.
    But I will try my very best to explaine

    Thankyou for taking the time to look at my problem.


    jmac1947, I have thought of dublicates. I Thought that there would be a way (FORMULA) to only show Unique values.
    I have attached a new file with some minor changes to the Userform. (Textboxes and a Big Listbox)
    As bakerman2 pointed out, It should be Comboboxes or TextBoxes, I went with Textboxes as you mention it in your last post.

    I would like, when searching for a produkt:
    While typing in first textbox "LEVERANDØR" it shows Unique values. So if I type DANA it will come up with one choise that will be "Dana Lim A/S"
    That will narrow down the products in next Textbox "PRODUKT" If I type "SEAL" it will list all "SEALFLEX HYBRID" products and i can choose any of these.
    In Textbox "TYPE" I will now only se the "600ML"

    Maybe it is better that the posibilities are shown in the listbox so I can click on them there.

    I hope this makes sence or I am ready to answer any questiens
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    Hi bakerman2

    I have replied jmac1947 and you in same post above.
    Thanks :-)

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform filter with criteria (Advanced Filter)

    First draft.

    Type dana in Supplier textbox => Listbox gets filtered on Supplier Name.

    Type sea in Produkt textbox => Listbox gets filtered on Produkts.

    Selecting an item in Listbox populates all other Textboxes.

    Deleting sea from Produkt textbox refilters the Listbox to all produkts of current Supplier so you can search for another produkt. (all Textboxes are cleared)

    Deleting dana from Supplier textbox refilters the Listbox to all Suppliers so you can search for another Supplier. (all Textboxes are cleared)

    It's not fully tested yet so there may be a bug or a flaw but that's for you to test.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    Thanks bakerman2

    It is great. There is one thing, I doent se "TYPE" in the listbox.
    If you type DANA in suplier and PROFF in product, there should be different choises in TYPE.

  9. #9
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform filter with criteria (Advanced Filter)

    Try this then.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Userform filter with criteria (Advanced Filter)

    Good morning bakerman2 (well at least it is morning in Australia )

    If I was a picky person I might suggest removing the first "*" from the filter statements to give a response that starts with the input values rather than contains the input values but that would be a very small nit picking comment.

    Your solution is excellent and I will keep it in my "examples of great code to solve various questions" files. I was on the right trail yesterday with the filtering concept (I was working on the original file and changing the listboxes to text boxes, not the subsequent one with text boxes and the list box) but did not have anywhere the level of knowledge you have.

    For me this was a great learning curve as I generally do not use "user forms" in my macros. Thanks for the effort you put in to the solution.

    Kind regards

  11. #11
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    That is very nice bakerman2
    I will study your code and se if I can figure out what you do.
    I can think of many uses for this and there are not many solutions that have met my needes on the forums.
    So thanks again.

    Just one thing(There is always "Just one thing")
    Is it possible to populate the textboxes while scrolling down in the listbox or clicking in the listbox.
    If this is possible and you are so kind to implement it, I will be very gratefull.

    Best regards

  12. #12
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform filter with criteria (Advanced Filter)

    Is it possible to populate the textboxes while scrolling down in the listbox or clicking in the listbox.
    That feature is already present since I posted the file in Post#7.
    Selecting an item in Listbox populates all other Textboxes.

  13. #13
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    Thanks for your quick reply.
    What I ment is for all 5 textboxes to be populated.




    Udklip.JPG

  14. #14
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: Userform filter with criteria (Advanced Filter)

    You'll see I've added a CheckBox on the bottom left corner of the userform.

    If you check this you'll be able to fill all TextBoxes by making a selection in the ListBox.

    Unchecking it will clear all TextBoxes, setting index of ListBox to -1 and you return to the searchfunction of the userform.

    Don't ask me to combine both because that could lead to unexpected behaviour of the userform. So it's either one OR the other function you wanna use.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    03-08-2018
    Location
    Denmark
    MS-Off Ver
    2016 for Windows
    Posts
    413

    Re: Userform filter with criteria (Advanced Filter)

    Thank you so much bakerman2

    Now I can fiddle around with it, and if I mess it all up, I wil call you for help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Advanced Filter Code : Criteria Range More Than 1 Row Breaks Filter
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-10-2021, 04:30 PM
  2. [SOLVED] Project using Advanced filter, Userform, and Macros
    By LTSSB in forum Excel General
    Replies: 1
    Last Post: 04-17-2018, 02:04 PM
  3. Replies: 0
    Last Post: 08-04-2017, 02:39 AM
  4. Advanced Filter displayed on Userform's Listbox
    By brainzlp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2016, 04:57 AM
  5. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  6. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  7. Userform: Make a advanced filter script "filter" when a combobox is altered
    By Zheno in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2010, 04:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1