+ Reply to Thread
Results 1 to 18 of 18

UserForm ComboBox Filter ListBox Display

  1. #1
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    UserForm ComboBox Filter ListBox Display

    Hi All

    Have attached a sample file. Am trying to display filtered data into listBox after [Cost Centre] Selection and [Dept] selection.
    Am able to filter [Boys] but no other....What am I missing.
    Please Login or Register  to view this content.
    Edit...
    Have tried below???
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 09-06-2017 at 07:09 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: UserForm ComboBox Filter ListBox Display

    On Module8, replace your code for Display with the following one and see if that works for you.


    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: UserForm ComboBox Filter ListBox Display

    Also it is a good practice to name the UserForm Controls to something meaningful. It is easier to debug the code if something is not working as expected.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    Hi,

    You can't assign a multiple area range to an array like that. (Your 'Boys' filter only actually works for the first area- it misses two rows off) You need to loop like this
    Please Login or Register  to view this content.
    I would also note that this is inefficient
    Please Login or Register  to view this content.
    as you look up the same value repeatedly. It is better to use Match to find the row, then just use that directly thus
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    @ sktneer...that worked on my incorrect use of code...
    @ xlnitwit...Did not even notice my errors after [boys] filter....Thanks for correcting my code...Works flawlessly.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    You're welcome. Thanks for the rep.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: UserForm ComboBox Filter ListBox Display

    You're welcome! Glad we could help.
    And thanks for the feedback.

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    Hey guys...I require further input pertaining to this post. If I would want to filter another after first Filter [Boys] i.e. Category Dropdown, how would I make this work.
    What the sheet currently does is filter [Boys], but when I select a Category, it filters just category and not a advanced filter on already filtered [Boys]
    I know that normally in code I would have a second filter field and criteria such as:
    Please Login or Register  to view this content.
    How would I do this with ComboBoxesI need to go down the comboboxes and narrow the filters down till [Design]

    Would i have to incorporate If Statements to check previous ComboBox selections i.e.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sintek; 09-07-2017 at 04:00 AM.

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    Hi again,

    Each combobox should only filter its relevant column. That way, changing one filter does not affect other filters.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    Hi xlnitwit

    If I however need to narrow the search as I go along .... as per manual filter on sheet, but only via listbox display?
    As per upload...If I select [Boys], all boys are displayed...If I then select [Tops], I want the filtered list of Boys to be narrowed to display only Boys / Tops
    Last edited by sintek; 09-07-2017 at 04:09 AM.

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    That's my point precisely. If the [Tops] combo only applies a filter to its column, any existing filters on other columns will be unaffected
    Please Login or Register  to view this content.

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    Sorry, don't get it...Surely like this?
    Please Login or Register  to view this content.
    I just can't grasp this...I need to filter first [Dept], then [Category] Then [Style] Then [Design] by selecting each dropdown after each other to have a final filtered list?
    Last edited by sintek; 09-07-2017 at 04:19 AM.

  13. #13
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    No, the Field number in both parts should be the same. Combobox22 only filters its own column. Assuming that is column 3, then you should use Field:=3 in both parts. Any existing filter applied to column 2 will not be affected by this.

  14. #14
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    K, so please be patient with me...
    If this is the code for the first ComboBox for [DEPT]
    Please Login or Register  to view this content.
    What must the code be for the next ComboBox i.e [CATEGORY] in order to narrow down the already filtered list further...
    Please Login or Register  to view this content.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    Assuming the next column, it would be
    Please Login or Register  to view this content.
    If you will have multiple columns to filter, I would suggest you refactor the filter code to a separate routine and pass the column and filter values to it as necessary from each combobox event.

  16. #16
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    I'm an Idiot, just noticed the filtering code as you explained works on the sheet...it is the display code that needs adjusting to now display the newly filtered range in the listBox????
    Edit...
    Nope that code works just fine...?
    Last edited by sintek; 09-07-2017 at 04:30 AM.

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: UserForm ComboBox Filter ListBox Display

    xlnitwit, Thanks for your valuable time....Look at this code of mine from uploaded sample...The reason it was not working as expected.
    Please Login or Register  to view this content.

  18. #18
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: UserForm ComboBox Filter ListBox Display

    You're welcome!

+ 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. Filter UserForm Multicolumn ListBox with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-07-2016, 03:12 PM
  2. Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-07-2016, 10:19 AM
  3. [SOLVED] Filter UserForm Multicolumn ListBox with TextBox/ComboBox
    By Alex.riccio in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2016, 04:31 PM
  4. Using ComboBox on Userform to filter ListBox
    By burger160 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-17-2014, 05:03 PM
  5. [SOLVED] Changed userform combobox to listbox, unable to get userform to retrieve datasheet values
    By dragonabsurdum in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2013, 01:38 PM
  6. Display info in Listbox/Combobox
    By Jakes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2011, 06:50 AM
  7. vba-Select from combobox,display in listbox
    By ACOM in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-16-2009, 03:46 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