Searchable Dropdown for VBA UserForms(Add in 30 Seconds)

[@passiveincome4863]

Hi Paul, did your VBA Fundamentals Course. Had a specific ques, your answer really solved my issue. Thank you. This guy really know what is is talking about. Highly recommended.

[@Excelmacromastery]

You're welcome.

[@Excelmacromastery]

Hope you enjoy this video. Let me know in the comments if you think you will use the Searchable Dropbox in your own projects. *IMPORTANT* -If you get an automation error it's because you don't have 3.5 of the .Net Framework installed.- -You can also avoid this error by setting the WindowsVersion property to False.- Update 30-01-2023: I have updated the source code so the .Net Framework is no longer required. This new version will work on both Windows and the Mac. No additional setting is required. The WindowsVersion property is no longer used so you can remove it.

[@saturdayscience5540]

There seems to be a bug. If I type mo in the search then the top and highlighted entry is "To Kill a Mockingbird by Harper Lee (55,606)". If this is the item I was searching for then the natural thing is to click the OK button. The message box shows I selected the book "mo". If after "To Kill a Mockingbird by Harper Lee (55,606)" is highlighted I click or double click the highlighted entry the search area does not update; again clicking OK returns I've selected 'mo'. I have to click on something I dont want and then back on "To Kill a Mockingbird by Harper Lee (55,606)" before OK returns the correct message that I selected "To Kill a Mockingbird by Harper Lee (55,606)".

[@jeffbrown363]

Amazing work, Thank You So Much! I am sure you are very busy but if you do get to read this, I would like to subsequently populate another search from some of the data in the selected row. How would you modify the line "frm.ListData = Sheet1.Range("A1").CurrentRegion" to reference a series of cells in the found row starting from a specific column?

[@pedromartinez1165]

There is no way to express how greatfull I am with this "GIFT", I'm a newbie to VBA and I really needed a way to search a huge cattle excel "database", this is just godsent. From today I'll start supporting your channel, is the least I can do. Thank you - Semper Fi

[@Excelmacromastery]

Glad you like it.

[@dazedsong]

I struggled a bit to get this to work on an existing project, but I did it at the end! thank you so much, the work that you do is amazing

[@malcolmt3125]

Can I ask, when you got it working on your own workbook, where did your data start, was it on this A1

[@ExcelForFreelancers]

Great Job Paul as always. I really enjoyed the training :)

[@Excelmacromastery]

Thanks a lot Randy. Appreciate it.

[@nafisahuq184]

The two MVP....

[@bryanstark324]

This is really useful! I have a list of patient names in my spreadsheet that I need to pull up a name to enter in a cell from a list of hundreds of names. Good solution!!

[@trevordaniels4443]

The searchable dropdown works great!! Hopefully you'll do more on userforms eg once the user clicks on something in the dropdown list (a product name for example) how do you populate other textboxes on the userform with information relating to that particular product? Everything I've read on this topic seems to use the userform for data entry which is then passed to an Excel sheet. I want to bring data back into boxes in the userform.

[@lacueva_v]

I am not a programmer; however you make me easy learn. Thank you to allow all to download your excel file with the code. I already customised the software for my app. Paul, well done, and please continue. BR from Spain

[@Excelmacromastery]

You are welcome!

[@wowJhil]

Never saw something this good while browsing KZbin before, this makes me even more interested in getting deep into the VBA possibilities!

[@Excelmacromastery]

Thanks a lot!

[@kolavithonduraski5031]

nice one 👍 i programmed a searchable list, when i in excel transformed a list in a Table, and then i used the Filterfunction of the table. ofc i used it with the wildcard for searching. for example when you search as an user "hello", vba is filtering the table for "*hello*". i didnt finish it i like your way and will definitly watch it in detail. thank you for sharing 👍

[@Excelmacromastery]

Thanks Kolavit. There were many ways to filter. I used an ArrayList in the end because it was quicker.

[@kolavithonduraski5031]

@@Excelmacromastery yep, this is definitly the fastest way. especially with the arraylist. i just did it the easiest way (regarding to my case to solve).😆 i look forward to work through your solution. Thank's Again 👍

[@pspicer777]

Hey. I liked pong! Getting back into Excel after a long hiatus. Your videos are excellent. Thanks for putting them together for us.

[@AnilKumar-vi8oe]

As great as always, valuable information... Thank you Paul

[@Excelmacromastery]

Glad you like it Anil

[@lalocalle8738]

Paul, muchas gracias. Trabajo muy prolijo, digno de mención de un gran profesional. Dios lo bendiga y guarde, éxitos renovados para su canal y carrera profesional. Algo hice mal, el formulario funciona correctamente, pero no así, el textbox, no despliega los resultados buscados. Alguien dispuesto a extender una mano? Agradezco de antemano. Gracia.

[@noambrand]

Wonderful. It would be even better if in addition to the search box option you had a drop down list with alphabetically ordered values (in case the user prefers to use the mouse without typing).

[@christophmayer2359]

Hi Paul, thanks for all the really good videos here. Back to this project & my question: when trying to start it I get the error „method or data object not found“ with an error in the „Private oEventHandler As New clsSearchableDropdown“ showing a problem with the method „itemsrange“. Looking through the class module I can’t find a method „itemsrange“. Could you please help me with this specific problem?

[@christophmayer2359]

Solution found (V 2.1). In the readme-file under „3.“ you need to replace „oEventHandler.itemsrange“ with „oEventHandler.List“ in the code to paste in. In the xlsm-files it ist updated already.

[@jamesthomas8249]

Could this code also be used to add a new Title? I have a list of temporary employees. The searchable drop down would work for entering data.

[@munimrashid7065]

This isn't just an amazing searchable dropdown but a great example of code reusability and separating UI from code as much as possible thus maximize productivity. This is the first lesson from you on UserForm/UI in this channel and hope more will be posted in coming days. Possibly "Mover Lists"* is another useful utility which can be implemented with a class like this one. Another could be "Sortable List" a ListBox with items, each of which can be moved upward or downward to rearrange/sort the list. * Mover Lists: exchanging items between to ListBoxes (1. Available items 2. Taken items). Move the selected one[s] or all in the other ListBox. Controls to take care of are: 2 ListBoxes and 4 Buttons [>] [>] [

[@billroberts4859]

Another good one Paul! Was worth the wait. Thank you.

[@Excelmacromastery]

Glad you enjoyed it Bill

[@dr.hamzamustafa4257]

Please... Can this work for listbox with multiple columns..... Mean more than one column

[@frederic4160]

I can not select my list to the dropdownlist. Is it in the code "frm.ListData = Sheet1.Range("A3").CurrentRegion" within the "CommandButton1". My list start in sheet1 in cel A3 and goes to A52. Thanks

[@ThereseMarieItabag]

Hi Paul..Thank you for all your tutorials, I really learned a lot...could you please make a tutorial on this one without using a class, just a normal sub and no overriding of events. Your approach is the fastest among tutorials that I've watched. I tried it with my 27k of rows and it seems very smooth. I have comboboxes that are dependent on the search result. Thank you.

[@WAIT4GOD]

Hello, this is really nice and helpful and thank you for all you do. What do I need to do extra to make this read from a different column? I changed it to read from D! yet it still reads from A1?

[@TheJaebeomPark]

Your content is very helpful

[@Excelmacromastery]

Glad you think so!

[@kerwinbram8722]

Excellent job man .Thank you

[@Excelmacromastery]

Thanks Kerwin

[@68team]

what can we use this feature with two different textbox and their search is range also different..pls reply

[@pravinrathod1625]

Hi, Can I select multiple values seperated by semicolon in this?

[@MichaelSandford-bo4xc]

Hi Paul, I like your videos and the way you explain the functionality of the code. The searchable drop-down box is quite intuitive. What I would like to know is can either the drop-down list box be parsed into colums? I modified the code in your video on creating a user form to edit a table and added the columns to the list box. I now need to search the data and present the results in coumns for selection of the row. Can you help with that?

[@peterwesterhout4538]

Works great, thanks. You're a true VBA grandmaster. Is it also possible to jump to the result in the list, instead of showing the book title?

[@thenebu]

Thank you ! Very useful, but how can I show 3 Columns in the listbox ?

[@thanhdoquang7830]

This a fantastic leverage. Thank you very much bro.

[@CampfireEditor]

I just watched the video but haven't digested it yet. I will be trying something like this in MS ACCESS. Thanks.

[@Excelmacromastery]

Glad it was helpful!

[@OmkarUmbre]

Hi Paul, I'm not able to download files. It takes me to the same cheatcode page. Please help :)

[@anburenganathan8369]

Hi paul, i want searchable drop down in user form combobox.. Here you are using list box.. Pls help on this

[@pedraodepirapora]

How should I do to list 2 columns in the ListBox?

[@sasasasa711]

its not working for me.! when I press run button, it highlights the ".itemsrange" of this section of code Public Property Let ListData(ByVal rg As Range) oEventHandler.ItemsRange = rg.value End Property and an error message says "method or data member "not found"....!!. Upon clicking OK, it highlights the first line of the above code. help pls..!

Same as mine

[@kaianten1326]

Hello Paul, very nice work. I found an error. When the user remove the starting text with Backspace the mid function get an error.

[@cyrilleonel5133]

I use your template and change the range from A1 to B1 where B1 column i let it empty for testing purpose but then when i run it, it still tracking the A1 column. How to solve it ?

[@tranbui6310]

Hi Paul, I did the same but confront error type mismatch when running the main sub-procedure. The error lies in the 2nd code, when I dim frm as userform1. How can I fix that? Thanks

[@theFactsPage]

Life Saver! Thank you so much!!!!

[@muhanadmawasalkazmeh137]

Thanks alot,can I get this searchable dropdown list in all the cells of excel sheet

[@laerciojunior2709]

Thank you so much for share this information.

[@ccustomcase5612]

Nice job, great tool. After I installed Net Framework 3.5, which I surprisingly did not had on the pc, the integrated ArrayList also worked.

[@Excelmacromastery]

Glad you got it working.

Hi and many thanks to you. Is there a way to select item from the listbox with the click of the mouse button?

[@Excelmacromastery]

Double click with the mouse

@@Excelmacromastery it does not work

Sorry for repeating but do you have any suggestions what could be wrong? Double clicking on the listboz does not carry the selection to the text box????

[@s1ngularityxd64]

😲next level vba

[@giorgiomya4546]

Astoundingly Excellent As Always! Thank You Very Much For Sharing!

[@Excelmacromastery]

My pleasure!

[@xenzomi1321]

This was very helpful. Thank you so much!

[@Excelmacromastery]

Glad it was helpful!

[@francoismarsau6412]

Amazing video as always. Can this code be adopted to use with data validation lists?

[@zapnick1]

Hi! Thanks for the project it was very useful, I was wondering if you could explain how can I use this code in a multipage configuration inside the userform. I keep getting a runtime error. Thanks again!

[@brianquinn3801]

Super slick! Is it possible to search multiple columns at the same time ? If you type letters/numbers in a box it will return all rows that have contain those letters/numbers? Thanks in advance.

[@malcolmt3125]

This is really good as a learning curve but, trying to implement it into my own workbook ! My data is not starting at A1, but C5, I can only see the reference on this line on the code added from your readme file " frm.ListData = Sheet1.Range("A1").CurrentRegion " changing this A1 location address does not point to my data, if I add sample data to say B1 or C1 I can get it to see it but not on a line 5 start.

[@shakhobiddinnakiev6767]

hello sir, what you have done here is fantastic, thank you vary muuch. One question please, what if want to change textbox to combobox ? can you please teach this also , thank you great!!!

[@felixthomas5476]

great video! Is it possible to work with multicolumns too?

[@trevordaniels4443]

The searchable drop down works great, however, if I put a button on the userform and place code into it's click event, nothing happens! An I putting the code in the wrong area of your code?

[@jesuscova2425]

Hi Paul, great Job!!. I found an issue...if the first key you press is not in the list I can still writing in the textbox and the message "No items found!!" does not appear. It should appear when the first letter is pressed. Best regards

[@zianurrohom1858]

Thanks paul 🙏

[@FMFT]

Excellent! What about a Library Management System?

[@momi3652]

How to copy this textbox and list box a range of cells?

[@user-vp6cm9le6q]

How does it work on all sheets?

[@philwells4683]

Hi I am having problems bringing up the list box and when using the arrow keys it comes up with an error 2110 can't move focus to the control because it is invisible, not enabled, or of a type that does not accept the focus - line of code its highlighting is .SetFocus on Private Sub List_MoveDown(). wondering if you can help, Many thanks

[@massimoollani7500]

Hi Paul thank you for sharing this great feature. I have a user form on word where the items of the drop down list are populated from an Excel file. Is it possible to create the same searchable feature on word too? Thank you. Massimo

[@quincyirang2543]

I cant run the useform. i havee debug on this Set coll = CreateObject("System.Collections.ArrayList")

[@RevolutionTeenMusic]

Thank you for sharing : ) I've a question, Why my Textbox1 always return "Type the item you wish to search for" even i select item from my range?

[@Excelmacromastery]

Have you double clicked or pressed Enter to select?

[@RevolutionTeenMusic]

@@Excelmacromastery I found a problem. I used Userform1 keyword instead of Me keywords.

[@filip3783]

Wonderful idea ! Exactly what I need for my UserForm assigning IT programming skills to Employees by HR support. Thank you Sir :)

[@Excelmacromastery]

You're welcome

[@ingenierialukso4386]

Hello Paul, and thank you so much for your work and help. I'm using your code but I need to call the list from an array, not from a range. I Don't know how to do that. I've tried to call my arrangement, but how you know, it doesn't work. I could write the info in a new sheet, to call the range, but it wouldn't be elegant. How could I do it?

[@luisng941]

Good morning, first of all thank you very much for the code, I'm a newbie can you give me the code to load two columns from the search in a listbox?

[@Ukdrama333]

Sir it is not working in enble of scrol lock buttone any option for that

[@brayandiazjimenez1420]

thank you for this project.

[@subramoniamm8629]

Great work I have saved the textbox value to database. when i try to retrieve data from database to textbox, I am getting the last alphbet of the data. Pls help

[@phongnguyenthanh9076]

Greate job, can i add a label to show how many results found beside "OK" button, or a bit description of selected item.I'll try. Thank you Paul !

[@Excelmacromastery]

Cam On Thanh. Yes but you would need to add a Label to the clsSearchableDropdown class and then connect it. Then when the textbox change event runs you would update the Label with the ListCount of the ListView.

[@siddhant_shirode_]

Hi, great work the searchable drop-down works great. But I wanted to have 3 such searchable drop-downs in the same userform and I'm unable to do it as the runtime error 2110 comes up. Hope you can help

[@Excelmacromastery]

I have updated the source code. Please download it again.

[@alexalexeenko2472]

Hi, your program is working fine. How do I get the cell number where the book name is?

[@michaelmaguire6709]

Very useful, thanks for posting, Pau

[@Excelmacromastery]

My pleasure!

[@Ali6824]

Thank you Paul for this. Not only is this code extremely usefull, it's beautifuly written too! Is it possible for way listBox to display multiple columns based based off of column 1' search criteria?

[@sebastian1200]

Great job! I like it a lot 👍.

[@Excelmacromastery]

Thank you! Cheers!

[@ceciliahernandez2708]

Paul: I'm trying to add this code to an application but, can't find a way to take the text in the textbox (selection) to a cell in a worksheet. Can you help me?

[@Excelmacromastery]

Hi Cecillia, You can use the .SelectedItem property. In the "Searchable Dropdown Settings Examples.xlsm" you can see this used in the UserForm Book property.

[@rbloesser9009]

The vertical scroll bar in ListBox1 never activates. Is there a solution to that? Otherwise, fantastic. !!

[@rbloesser9009]

Sorry nevermind - check the "show all entries", scrollbar works great

[@Excelmacromastery]

Glad you like it.

[@ImprovisedExpletiveDevice]

Any chance I could get a copy of the class module? The link does not appear to trigger the download anymore.

[@Excelmacromastery]

Hi Alan. I've added some small updates to the code. Get the new version from the description.

[@ImprovisedExpletiveDevice]

@@Excelmacromastery I'm still not getting the e-mail. It's possible it's getting filtered but I can't find it any where if it is.

[@Excelmacromastery]

Send me an email. Paul@exce...

[@learningenglish9405]

Thank you so much !

[@jrcryo]

Hello, i have an error when running the code error 440 in function FilterDataWindowsOnly Set coll = CreateObject("System.Collections.ArrayList") i guess that i am missing some of library

[@Excelmacromastery]

I have updated the source code. Download again for the latest version.

[@nareshkumar9647]

I requested for the code and received message that it was sent. But I did not receive it. Kindly check.

[@JimChampaigne]

I receive an error "method or data member not found" Public Property Let ListData(ByVal rg As Range) oEventHandler.ItemsRange = rg.value ' from install notes '''oEventHandler.ItemsList = rg.value ' from clsSearchableDropdown End Property Which line is correct? Program was called by running macro MAIN

[@Excelmacromastery]

Change ItemsRange to List. I have updated the readme file with this change.

[@dandeman1148]

The version Searchable Dropdown Settings Example worked ok but Searchable Dropdown Simple and when I tried to add to a blank workbook, when userform opened and I tried typing a search it says Invalid use of Null or MEthod of Data member not found and highlighted .itemsrange in the public property LetListData

[@Excelmacromastery]

Sorry Dan, .ItemsRange should be changed to .List. My bad:)

[@joaocustodio2094]

Thanks Paul.

[@taylorbomben686]

Hey Paul. This is a amazing, thank you for sharing this. I want to use this as a FAQ portal and was wondering how I can change the code so that when I search and find the question I am looking for, it takes me to the answer. The answer would be on the same spreadsheet as the question and I was just going to make a group on each question so it becomes collasable. Is there anyway you could please help

[@joaocustodio2094]

Another great job.

[@Excelmacromastery]

Thanks again!

[@andrianidanol6795]

Good evening Sir I downloaded the file but it is not working.is it because I am using a standard window and not mac and Office 365 disabled the Macro it cannot run.please HElp

[@Excelmacromastery]

Hi Andrian, I have updated the source code and it should work now. Please download the code again.

[@mikeglassman5572]

Excellent idea and method

[@Excelmacromastery]

Thanks Mike

[@alwilson6471]

Could I use this in other MS Office user forms, Outlook VBA for example?

[@Excelmacromastery]

It needs slight changes. Change the color references from rgb to vb and remove the FilterDataWindowsMac function and the call to it. This is because that function uses the Worksheetfunction Filter.

[@Fain82]

Hi Paul, i love your Videos and your Passion for vba. Don‘t know it is mentioned Before But just wanna let you know that the „No item found“ case is Not displayed in the Listbox when the First entry is Not matching with any letter. In This Situation the listbox never will be set visible.

[@Fain82]

Possible solution could be to Check if the Current text is in equal to m_startText in the UpdateListbox function. As a resultat the listbox should be set visible

[@Excelmacromastery]

Thanks. I checked that and it works for me. I have updated the code on the 30-01-2023. Are you using this version or an older one?

[@Fain82]

I use version 2.0. i have Downloaded it on 23th Jan 2023. Do you consider to embed your clsData structure for multi dim array usage or is it to slow?

[@syedmuhammadhaseeb8021]

Wonderful work Thanks a lot for sharing this. As the same thing I was trying with Combo box but google was unable to help in this regard. I have successfully installed this text box in my own project and passing the value from text box to my data table. I want to ask 2 things how can i use same module and coding on same form for other text boxes. Second I am unable to find right code for the frm.List Data = for my structured table column. instead of using Current Region as my dropdown list is in a table column. So I wanna use it.

[@mike_case]

Great video :) Thank you Paul.

[@Excelmacromastery]

You're welcome Michal

[@marloncely]

Amazing thanks a lot for sharing.

[@luisgil8589]

Excellent information Paul, the bad thing is that you do not answer the email. No matter; I just wanted to point out that instead of following the instructions in the video to make our new worksheet to use your excellent work, just put our data in column "A" of your file (the lazy way).

[@markchap13]

This is a fantastic accomplishment! Is there a way it will work for data types? It seems like it might be getting fooled by the data type symbol before the text in the cell. I tried it with the Stocks data type. Thanks!!

[@Excelmacromastery]

Hi Mark. You would have to alter the code to get it to work for different data types.

[@oguzturkyilmaz543]

Thank you very much.

[@muzakhirhakim8427]

how about multiple textbox, how to do?

[@user-vp6cm9le6q]

How to search multi sheets

[@zoranmirkovic9859]

Thank you Paul. You did a great job. One question. When I click (select) on first item in listbox just after type something in textbox, textbox is not populate with value of first (selected) item. I have to select second or third item in listbox to populate textbox with value of them, and after that I can select first item to populate textbox. How to fix this?

[@anshulgupta3735]

Hello Zoran, Did you get the resolution for it?

[@zoranmirkovic9859]

@@anshulgupta3735 No, I didn’t

[@chandraamgain4041]

Hello I want someone to help me to add the searched item into a selected cell

[@mohammedraisdeen6328]

Dear Sir, amazing project. Sir! if it is possible to use this searchable dropdown for multi-columns

[@brandonwilburn1589]

Can this be used with a data model table?

[@ricos1497]

You could, but you'd have to adapt it. The easiest option would be to download the data model table to a sheet and use the method in the video. The nicest option is to bind the list box to a recordset. If you use ADODB connection (set conn = Activeworkbook.Model.DataModelConnection.ADOConnection) then you can work nicely with the data model in the same way as you might a SQL query, but you've done all the transformation stuff in Power Query. Where you'd normally have a SQL string, you'll need an MDX string instead. For example: strMDX = EVALUATE myTable will return an entire table, with EVALUATE CALCULATETABLE (myTable, myTable[myField]="123") returning those 123 records. I've made a massive assumption that you have tried to access SQL with VBA before in my response! If not, look that up and take an example that returns a recordset and then replace with my connection string above and the EVALUATE query.

[@ricos1497]

Okay, okay, I'll give you some code! Sub form1_initialize() Dim i As Integer Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim strMDX As String On Error GoTo FailedOutput Set conn = ActiveWorkbook.Model.DataModelConnection.ModelConnection.ADOConnection 'strMDX = "EVALUATE myTable" strMDX = "EVALUATE CALCULATETABLE(myTable,myTable[Column1]=""123"")" Set rs = New ADODB.Recordset rs.ActiveConnection = conn rs.Open strMDX, conn, adOpenForwardOnly, adLockOptimistic 'Debug.Print rs.Fields(0) ' Output of the query results with me.Listbox1 Do Until rs.EOF .additem = rs.Fields(i) rs.MoveNext Loop end with rs.Close Set rs = Nothing Exit Sub FailedOutput: MsgBox Err.Description End Sub

[@brandonwilburn1589]

@@ricos1497 great stuff! I’ll have to it give it a try. Thank you for the code.

[@Excelmacromastery]

Nice work Rico