Это видео недоступно.
Сожалеем об этом.
Searchable Dropdown for VBA UserForms(Add in 30 Seconds)
HTML-код
- Опубликовано: 3 авг 2024
- 👉 Ready to master VBA?
- Check out my full courses: courses.excelmacromastery.com/
- Subscribe to the channel here: bit.ly/36hpTCY
-Want to download the source code for this video? Go here: bit.ly/2ZLJiNo
(Note: If the download page doesn't work then make sure to turn off any popup blockers)
Searchable Dropdown for VBA UserForms(Add in 30 Seconds)
In this video I'm going to show you how to add a Searchable dropdown to any VBA Userform. The best part is you can do it in less than 30 seconds.
The best part is that it doesn't involve an installation. All you need is a simple class module which you can download from below.
Useful VBA Shortcut Keys
========================
Debugging:
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)
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.
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)".
Hey. I liked pong! Getting back into Excel after a long hiatus. Your videos are excellent. Thanks for putting them together for us.
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
Can I ask, when you got it working on your own workbook, where did your data start, was it on this A1
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.
You're welcome.
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
Glad you like it.
Another good one Paul! Was worth the wait. Thank you.
Glad you enjoyed it Bill
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
You are welcome!
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!!
This a fantastic leverage.
Thank you very much bro.
Great Job Paul as always. I really enjoyed the training :)
Thanks a lot Randy. Appreciate it.
The two MVP....
Astoundingly Excellent As Always! Thank You Very Much For Sharing!
My pleasure!
Wonderful idea ! Exactly what I need for my UserForm assigning IT programming skills to Employees by HR support. Thank you Sir :)
You're welcome
This was very helpful. Thank you so much!
Glad it was helpful!
As great as always, valuable information... Thank you Paul
Glad you like it Anil
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.
Life Saver! Thank you so much!!!!
Excellent job man .Thank you
Thanks Kerwin
Great video :) Thank you Paul.
You're welcome Michal
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 [>] [>] [
Amazing thanks a lot for sharing.
Thank you so much for share this information.
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.
Glad you got it working.
Thank you so much !
Thanks Paul.
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.
Your content is very helpful
Glad you think so!
Very useful, thanks for posting, Pau
My pleasure!
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).
Thank you so match, Sir.
thank you for this project.
Great job! I like it a lot 👍.
Thank you! Cheers!
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.
Thanks paul 🙏
Thank you very much.
Never saw something this good while browsing RUclips before, this makes me even more interested in getting deep into the VBA possibilities!
Thanks a lot!
Very useful.
Another great job.
Thanks again!
Teşekkürler. harika bir yöntem ile ustalık :)
"Thanks for the great method :)" - You're welcome.
😲next level vba
I just watched the video but haven't digested it yet. I will be trying something like this in MS ACCESS. Thanks.
Glad it was helpful!
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 👍
Thanks Kolavit. There were many ways to filter. I used an ArrayList in the end because it was quicker.
@@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 👍
Excellent idea and method
Thanks Mike
Wonderful
Great Work
Thanks
It's great job 👍
Thank you! Cheers!
thank you teacher.
You are welcome
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?
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?
Amazing video as always. Can this code be adopted to use with data validation lists?
Amazing!!!!!
Thanks!!
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!!!
great video! Is it possible to work with multicolumns too?
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?
Legend
Thanks alot,can I get this searchable dropdown list in all the cells of excel sheet
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
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.
Awesome
Thanks Ayush
Wow, this is amazing. It will help me a lot. Thanks
Which font are you using for VBA code, that looks awesome.
Glad you like it. Consolas font.
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!
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?
I complite my vbaform with yor code sir thanks
Hi, your program is working fine. How do I get the cell number where the book name is?
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).
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.
Thank you ! Very useful, but how can I show 3 Columns in the listbox ?
👌👌👍👍
Excellent!
What about a Library Management System?
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?
A searchable dropdown in a cell would be very useful
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
Dear Sir, amazing project. Sir! if it is possible to use this searchable dropdown for multi-columns
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
Paul, buenos días de Dios. Muy bueno el trabajo del contenido y muy útil. Gracias por el aporte profesional.
Excel no permite importar archivo; msg: archivo de alto riesgo; quedó bloqueado el archivo. Que solución sugiere? Gracias. Que tenga un hermoso y bendecido día. Éxitos renovados para su canal y profesional.
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?
Hello Paul, very nice work. I found an error. When the user remove the starting text with Backspace the mid function get an error.
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
👍👍
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.
Hi, Can I select multiple values seperated by semicolon in this?
I love this project. I could not get it to work because of automation errors. I am trying to incorporate this into an inventory system where I will need to search for thousands of items. I am also interested in deploying dictionaries to keep track of inventory levels (adding to, and removing from an inventory). I would even be willing to pay for a course that would help be in my understanding of building applications.
I have updated the code. Download it again and it should work fine.
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!!
Hi Mark. You would have to alter the code to get it to work for different data types.
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 !
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.
Hey Paul, This is really awesome, Love the content you pot on. It's great learning for me.
I am facing an issue, trying to debug it but could not find it being an amateur. The first item in the filtered listbox is not getting populated to textbox on clicking on to it in first instance(it does populate using enter button in first instance) but if I use down and up keys and select the first item after moving the selection through the filtered listbox, it does populate to textbox by clicking too.
Also, If there is only one item in the filtered list box, then clicking won't work at all. e.g. : if the type "Order" there will be only one item in the filtered listbox.
Please help me find out a resolve to it. Thanks in advance.
Please help me in this issue. Been trying to understand but no luck yet.
@@anshulgupta3735 Did you ever solve this issue? I am having the same trouble.
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.
what can we use this feature with two different textbox and their search is range also different..pls reply
Hi and many thanks to you.
Is there a way to select item from the listbox with the click of the mouse button?
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????
Hi paul, i want searchable drop down in user form combobox.. Here you are using list box.. Pls help on this
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 ?
Sir, I want to say, For last few days, I got stuck creating a VBA Code where I've to compile all the Data from Different Files. For your information, all the files are in the same folder. But, there are some catches, -
001) Each File has some Common Headers and some Uncommon Headers. I've to compile the Data Header wise.
002) Each File has more than 80,000 Data. And there are 28 to 31 files in that Folder depending on the number of days in that specific month. Now, during the compilation, if the row of the Master File exceeds 1 Million, then save it and close it. Create a new Excel Workbook, where compilation begins again. But, this time compilation should start next after where it ended in Last master file.
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?
Please... Can this work for listbox with multiple columns..... Mean more than one column
How should I do to list 2 columns in the ListBox?
Great job, but let me just want the data range to be one column, as it doesn't search in all the columns of the sheet.
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
I have updated the source code. Please download it again.
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?
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.
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
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
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?
Hello Zoran, Did you get the resolution for it?
@@anshulgupta3735 No, I didn’t
This guy needs to run for PM
lol
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?
Have you double clicked or pressed Enter to select?
@@Excelmacromastery I found a problem. I used Userform1 keyword instead of Me keywords.