Sometimes you can have a large number of options to show to a user and you want them to select just one. You can, of course, show them a UserForm with a ListBox (or ComboBox), populate the ListBox (or ComboBox) with each item and ask them to select one. This is fine for a small number of selections that would be visible to a user without having to scroll up/down.
However, if you have many items to show (as in the example, below) then a better option can be to show a UserForm with a ListBox which can be filtered by typing one or more characters into a TextBox. Like this ... without any filter being applied ...
... and the same UserForm with a filter being applied ...
To do this, you first need a UserForm (in the example code below, the UserForm is called UserForm1) with a TextBox named TextBox1, a ListBox named ListBox1 and a CommandButton named CommandButtonOK with its Caption property changed to "OK". The logical layout, I think, is as it is in the above two images ... but you can lay them out as you wish.
The ListBox should have only 1 column (i.e. its ColumnCount property should be set to 1) and should be 'single select' (i.e. its MultiSelect property should be set to 0 - fmMultiSelectSingle).
Bare bones ... only the filter functionality
The following code is very much "bare bones" so that you can see the key elements. This does not include a "hint" in the TextBox (as you can see in the upper of the two above images) but the filter code is in place. Nor does it include various other functionality you would normally want to include (for example, the CommandButton does nothing here).
In the code-behind of the UserForm, add this:
This code should be fairly self-explanatory and is commented for clarity, but essentially when Display() is called, the values to be shown in the ListBox are assigned to maValues, we then show all (it is all because there is no 'filter text' in the TextBox initially ... it is, of course, empty) of those items in the ListBox by calling UpdateListBox(). Then, every time the text in the TextBox is changed, we again call UpdateListBox() to update the items shown. UpdateListBox() contains the filter code such that items are shown in the ListBox if either no filter is present or if the text of the item contains the filter text.
Here, then, is some code to create some values to show in the ListBox and to display the UserForm (add this to a standard Module):
... run this to display the UserForm. Use the red 'X' to close the UserForm once you are done.
Fully fleshed-out functionality
This code is much more like the code you might want to use in reality. It includes a "hint" in the TextBox, allows the selection to be returned to the calling code when "OK" is clicked, and allows a double-click on an item within the ListBox to be automatically selected (ie and close the UserForm without having to click on "OK"). It also blocks the red 'X' from closing the UserForm without the user having made a selection.
Change the code in the code-behind of the UserForm to this:
Again this code should be fairly self-explanatory and is commented for clarity. The core functionality works just the same as for the 'bare bones' version but we've added:
GetSelected() so that calling code can get the selected item
ShowOrHideHintIfAppropriate() which, no surprise, shows or hides the hint in the TextBox
Various event handlers for the TextBox gaining/losing focus, the CommandButton being clicked, the ListBox being double-clicked and, finally, the UserForm being 'QueryClose'd which allows us to prevent a click on the red 'X' from closing the UserForm
Here is some code to create some values to show in the ListBox and to display this version of the UserForm (replace the code in the standard Module with this):
... run this to display the updated UserForm. Use the 'OK' button (or a double-click of an item in the ListBox) to close the UserForm once you are done.
Anything else?
If you want to allow wildcards in the filter text ... try using Like instead of Instr() - e.g. replace
... with ...
... just remember that, without including Option Compare Text, Like is case sensitive (or use LCase() on both Strings).
And if you want to make the CommandButton responsive to the mouse hovering over it, see my post Changing UserForm Button colour on mouse hover.
And if you want to use more descriptive names for the UserForm, TextBox or ListBox (or anything else!) ... consider using the Rename command of VBE_Extras which will rename not only the UserForm / TextBox / ListBox but also all references and event handlers.
Comments