+ Reply to Thread
Results 1 to 5 of 5

Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

  1. #1
    Registered User
    Join Date
    11-29-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    7

    Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

    Hi Everyone,

    I've been looking for an answer to this specific scenario and I haven't found it yet. If it exists, I'm sorry for wasting your time and would request the link to that answer please.

    If not, here we go.

    I'm creating a listbox in a userform that's using a rowsource from a sheet to list the multicolumn list.

    This list is very big and I want to be able to filter through it using a combobox to specify which column to filter by and a textbox to specify the keywords to filter by.

    I've found different iterations of these in pieces but not as a whole and I haven't been able to put it together.

    Here is the code I have so far:

    Please Login or Register  to view this content.
    I get a "Unspecified Error" (80004005) When I get to the ".RemoveItem i" line which I've read is because I'm using RowSource to populate the list.

    Is there an easier what to achieve all this?

    Thank you for your help and let me know if you need more information.
    Last edited by Alex.riccio; 12-07-2016 at 08:46 AM.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

    Alex,

    Unless I'm very much mistaken you started the same thread yesterday?

    You cannot remove items from the listbox if you use a rowsource... instead you'd have to populate the combobox by code (for example in the userform initialize code). Then you would be able to remove items from it.
    Last edited by Arkadi; 12-07-2016 at 09:39 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    11-29-2013
    Location
    Ottawa
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

    Thank you for your reply Arkadi! I was having trouble yesterday and the site was timing out so I didn't think it had posted my question yesterday. Sorry about that.

    That's what I've come to the conclusion is that the Rowsource is the issue but I'm not sure how to go around that.

    I would need to populate the ListBox using a multi dimensional Array (I have 29 columns in the ListBox) and use the filter to only populate the Array with the values I want, and then repopulate the listbox... But this seems very complicated or process intensive if the list is very long (3000+ rows).

    Is there a way to populate an Array using the same amount of columns as on the sheet dynamically?

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

    Yes you can dynamically set columns... it is the .ColumnCount property of the listbox.... I'll post a snippet of code that can hopefully point you in the right direction...

    Please Login or Register  to view this content.
    Last edited by Arkadi; 12-07-2016 at 10:16 AM.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,014

    Re: Filter UserForm Multicolumn ListBox. populated by RowSource. with TextBox/ComboBox

    I should have added that the column widths could be a pain to set if it is dynamic... but if they are all the same width you could use this loop.... you could also ammend it to do specific widths for specific column numbers

    Please Login or Register  to view this content.

+ 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] Sort for multicolumn listbox in userform (5-columns)
    By d.sanchez in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-11-2017, 05:30 AM
  2. 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
  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. Loading MultiColumn Combobox in a Userform
    By anandvh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-22-2014, 10:47 AM

Tags for this Thread

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