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:
A more complete example would be as follows:
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.
Bookmarks