+ Reply to Thread
Results 1 to 2 of 2

Filterable listbox

  1. #1
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Filterable listbox

    As part of a project for some of our customers, I've had to make a userform with a filterable listbox. In this particular scenario, I can't guarantee the order of the columns, so I came up with a general dataview that allows filtering and re-ordering of columns. I'm sharing it as wanting a with multiple columns that can be shared comes up as a common request - this abstracts the hard bits away to provide an easy interface.

    At its simplest, with a textbox and a listbox, this is all that's needed:

    Please Login or Register  to view this content.
    A more complete example would be as follows:

    Please Login or Register  to view this content.
    Providing the FilterColumnsNamesArray property allows for the re-ordering of columns as well as excluding columns (or including multiple times).
    Providing the SearchColumnNamesArray property explicitly sets columns as searchable (excluded columns are ignored), the default is to exclude all columns

    Formatters allow the formatting of data in columns within the listbox by column name. This means that only the underlying data is searchable, not the resulting formatted string - the FilterSet is provided with an interface so it would be trivial to provide another implementation to search after formatting. This would actually be more efficient as the formatters could be applied when the data is loaded, rather than as it is displayed.

    There may be some bugs in this, it is not thoroughly tested and extracted from a much larger solution - use at your peril!

    I've tested this with 10k records and it is performant, however there are various improvements that could be made to increase performance if it is an issue.
    Attached Files Attached Files
    Last edited by dominicb; 12-15-2018 at 09:22 AM. Reason: Corrected typo at Kyle123's request

  2. #2
    Registered User
    Join Date
    03-27-2020
    Location
    Dublin
    MS-Off Ver
    Excel 2016
    Posts
    1

    Re: Filterable listbox

    Hi Kyle,

    This is an awesome piece of code. Big thumbs up for this.
    I am still tying to get myself through the code, to understand it and then addapt it for an inhouse project.
    However one thing I have noticed, if there is no filter added, then the last row will not shown in the list.
    I have tried to figure out what is the problem, but unfortunattely I am not as skilled in VBA as I would like to be.

    Regards,
    Greg

+ 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] Trouble Creating Filterable Line Graph
    By ronburgundy37 in forum Excel General
    Replies: 7
    Last Post: 05-31-2017, 10:50 PM
  2. multiple info searchable or filterable in column
    By swfarm in forum Excel General
    Replies: 1
    Last Post: 03-06-2017, 12:49 PM
  3. re-arranging cells but still filterable?
    By 20GT in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2016, 06:41 PM
  4. [SOLVED] if anything in listbox select first item, if listbox empty do nothing (listbox in userform
    By mcdermott2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2015, 12:49 PM
  5. Listbox to Listbox, no duplicates & submitting same UserForm data for each Listbox entry.
    By jamieswift1977 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-18-2012, 12:18 PM
  6. Chart with filterable legend
    By stuartmarsh in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-12-2006, 04:47 AM
  7. Replies: 0
    Last Post: 03-13-2005, 02:06 PM

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