Searchable Dropdown for VBA UserForms(Add in 30 Seconds)

in this video i'm going to show you how to add an amazing searchable dropdown to any vba user form in less than 30 seconds if you're like me and you love creating high quality vba applications then you will be blown away by this functionality this is the standard combo box for the vba user form if you type letters it will bring you the first item that starts with the letters that you type by modern standards this behavior is very limited which is why i created the searchable dropdown this is the searchable dropdown i'm going to show you how exactly a searchable dropdown works and then i will show you how to install it to any user form in less than 30 seconds i've got a list of a thousand books and i want to find say the books with castle in the title as i typed the letters of castle you can see that it filters the list and only matching items appear if we like we can use the arrow keys to move up and down the list like this we can then press enter to select the one that we wish to use the searchable dropdown has some other useful features as well watch now as i change the value to 10 and you can see that 10 items are now displayed another feature of this drop down is the ability to display all the matching records this means it will still show the top matching items but you can use the scroll bar to view all the other matches in the list a third feature is case sensitivity watch as i type harry in lower case you can see that it only brings back matches that are lower case if i turn off the case sensitivity then it returns all matching harrys no matter what case they are now creating the searchable dropdown required a lot of complex code however i've packaged it into a single class module so that anyone can install and use it with a few simple steps so let's go ahead and install the searchable dropdown in a brand new workbook imagine you have a workbook with a list of sci-fi movies like this and you also have a user form and you want to add a searchable drop down for the movies to this user form so this is how you do it step one download a searchable dropdown from the description below the video step two extract the three files to the folder of your choice and then drag the searchable dropdown class module file to the project like this step 3 add a new text box and list box to the user form by dragging them from the toolbox now the position of the list box doesn't matter as it will be positioned automatically step 4. open the readme file then select and copy the code from step 4 in the readme file then switch to the user form and press f7 to view the user form code then paste the code here step 5 go back to the readme file and select and copy the code from step 5. we will copy this code to a standard module it is used to display the user form now we have a working searchable dropdown control let's see it in action by running the code we click in the standard module sub and press f5 to run the code if i type the word alien you can see it filtered the data in the list box as i type we can then select any one we want by using the arrow keys and pressing enter now that you've seen how to add the searchable drop down functionality let me show you some important things to note so you will get the most out of this control so now i'm going to explain exactly how the code works it's actually a lot more simple than you might think you don't need to know anything about class modules to use it there are three parts to using the searchable dropdown class part 1 is setting up the class module object part two is assigning the controls and the data that you're going to use part three is configuring the settings which in most cases we may not need to change at all let's look at each of these in turn we declare and create the variable as a private member of our user form like you can see here we can now access the class by using the o event handler variable when we are finished with the variable we set it to notting to avoid memory issues and we do this in the terminate event so this is a sub that runs when the user form closes so this is all we have to do with the variable so once we have access to the variable we need to connect the class to the text box and this box controls we can then use our searchable dropdown class to override their standard behavior these lines here attach the text box and list box to our searchable drop down class one thing to keep in mind is that if you change your text box or list box name then you must change it here as well for example if we change the text box name to textbox movie and compile the code we get this error but now if we change the name it works fine the list property that you can see here is used to set the list of items that will be filtered by our control i have set it here in the list data property this allows us to set it from the code that displays the user form we create our user form and then on this line we pass a range of data to the user form we then in turn pass this in the property to the searchable dropdown class like you can see here let's take a look at the settings which are the most interesting part of the searchable dropdown as they allow us to alter the behavior and do some really cool things these settings that i use here are set to the default value so if you delete them the searchable drop down will still work the same way i've put them here so that you can see how to easily change the behavior of the searchable drop down by just changing values so let's look at max rows this is the number of rows that is displayed in the control this is how it works if we type al then the first six items that contain al are displayed in the drop down as you can see here now we set the max rows to 10. when we run the code again you will see that typing al will now display 10 items instead of 6 which we saw previously one thing to remember is that if the number of items is less than the max rows then only this number of items will be displayed for example if we type al space then only two items will be displayed even if the max rose is set to 6 or 10. show all matches means that all items that match are loaded to the drop down however only the specified number of rows are visible at any time let's take a look we set max rows back to 6 and we set showall matches to be true we run the code again now when we type al you can see that it shows us 6 items but we can see that we've got a scroll bar on the right hand side now when we go down to the scroll bar you can see that it lists all the items that match a l the next thing we can set is the case sensitivity of the search to set it we set compare meta to vb text compare our to vb binary compare at the moment it is set to text compare so this means it's not case sensitive currently if we type something like space it brings us back all the titles containing the word space even though you can see somewhere uppercase and somewhere lowercase this is the default but if we want to make our filter case sensitive then we can use vb binary compare instead of vb text compare let's change this to vb binary compare and run the code again now we type space in lowercase letters and you can see it only displays the titles where the word space is in lowercase this time we use uppercase letters and you can see it only brings back the title where space is all uppercase if we type just the first letters as uppercase you'll see that it brings back only the ones where case matches so this is how we do case sensitivity now the last setting windows version you can ignore most of the time if you're using a mac then you should set this to false otherwise you can leave it as true i've included an example workbook in the download so you can easily try out the settings for yourself without having to keep stopping and starting the code as you can see here in this example remember you can get to download with this example and the searchable dropdown code from the description below the video i used class modules to implement this dropdown if you'd like to learn more about class modules then check the playlist on the screen to learn more if you enjoyed this video then please click on the like button and if you'd like to get notified of my upcoming videos then please click on the subscribe button and the bell icon beside it thank you for watching

Channel: Excel Macro Mastery

Views: 23,194

Rating: 4.9528403 out of 5

Keywords: VBA UserForm, VBA Searchable Dropdown, VBA Combobox, VBA Dropdow

Id: gkLB-xu_JTU

Length: 8min 38sec (518 seconds)

Published: Mon Mar 01 2021