Autocomplete Entries With Excel Drop Down List
HTML-код
- Опубликовано: 28 июн 2013
- To make data entry easier, create a drop down list in an Excel cell, using data validation.
This saves you time, and prevents errors, but has some limitations.
-The font size is small, and can't be changed.
-You can only see 8 items at a time, and have to scroll to see the other items.
-There's no autocomplete - pick from the list, or type the full entry.
This video shows you a workaround for those limitations. --Put one hidden combo box on the worksheet
--Double-click a data validation cell, and the combo box appears
In the combo box:
-you can adjust the font size, and the number of rows. -entries autocomplete as you type
Go to my website, and get the completed workbook from this video
www.contextures.com/xlDataVal1...
For the example that works when you single-click on a cell, go to this page:
www.contextures.com/xlDataVal1...
Instructor: Debra Dalgleish, Contextures Inc.
Get Debra's weekly Excel tips: www.contextures.com/signup01
More Excel Tips and Tutorials: www.contextures.com/tiptech.html
Subscribe to Contextures RUclips: ruclips.net/user/contextu...
#ContexturesExcelTips
'-----
VIDEO TRANSCRIPT - Abridged
To make it easy for people to enter data in Excel, you can create drop down lists of items.
In this cell, we have a list of weekdays. I click on that Arrow, click a weekday, and it fills in the cell.
The same thing for months. Here's a list. It only shows 8 items at a time, but I can scroll down and click December.
Those lists are on another worksheet, and then use data validation, on the Data tab, to set that up.
There are a few limitations though. The font is very small and you can't change it.
And you can only see eight items at a time.
This doesn't auto complete. Data validation doesn't help you fill in the word.
Excel might help you, if it can copy something that you've already entered above, but the data validation itself won't help me type January.
If I start typing, I have to fill in the whole word and move on to the next cell.
So data validation is helpful, but I've come up with a work around that you can download on my website and it uses a combo box over cells that have a data validation drop down.
In this corner, hidden away, is a little combo box, and it pops up over a cell, when you double click on it.
So if I double click here, the Combo box comes out of hiding, goes over this cell, and picks up the same data validation list that the cell uses.
So when I click the arrow, I'll see the list of weekdays. I can click on one, press Enter to go to the next cell.
I'll double click again, and for this one I could start typing T, and it fills in Tuesday, but if I wanted Thursday, I'll type in h, and then press Enter.
So it helps with the typing, and if we look at the months, I've changed the number of rows that show, so we can see 12.
You can adjust that setting, as well as the font size, and select. And again here, we can type.
So if I want to type August, just have to type two letters and it's filled in for me.
To see the Combo box, I'm going to double click on a cell and then go to the Developer tab on the ribbon, and there's a Design Mode button.
When I click that, I'm able to make changes to that Combo box. I can click on the Properties button and here are all the things you could change about that Combo box.
So you get a lot more control over it than you have over data validation.
So you could come down here, and go to the font. Instead of Arial font, you could change the font, and the font size, to whatever suits you.
And here's where I changed the list rows. This is 12 you could make that a larger number. You'd want to pick a number that's going to fit on the screen. You don't want anything that's too huge, but you can adjust that setting.
And then when you're done making changes, turn off Design Mode, and you're ready to go again.
So if you download the sample file from my website, you'll be able to copy it all into your workbook.
Copy the Combo box and the code onto the worksheet. So if I right click View Code, you'll see that there's code that runs, when you double click the cell.
You don't have to understand all this code, you would just have to get it into your workbook.
For more Excel tips and tutorials, and to download the sample file for this video, please visit my Contextures website, at www.contextures.com 
Наука
Thanks so much for taking the time to make this guide, but most importantly - for providing the download so I can see the code. It's very easy for people proficient in something, to teach in a way where you miss out crucial steps that leave beginners like me in a state of frustrated anger. With the code, one can reverse engineer and feel good about life again! All the best.
You're welcome, and thanks for letting me know that the code in the sample file helped you!
Your website went into ALL the details. thank you for the full content overview and means of applications!
I am from India and I had made an excel data entry sheet for my GST Invoice. I was facing the biggest problem, that the dropdown list was not able to autocomplete. I tried many websites and blogs about the combo box but none helped. This video helped me. Also, I am not an expert in Advance Excel. Thank you very much. I don't have any words about how to thank you further. Superb.
Hi! Thanks for the detailed tutorial! I just have a question, is it possible to activate the auto-complete based on single click and not double click (on the data-validated cell)?
Thank you so much
I have a large drop down list and find an item in that is time consuming. these codes are really helpful.
Thank you very much for sharing this!! #14 had exactly the functionality I was looking for :D
brilliant! i've just followed this and it's worked for me. i'm not very VBA savvy, so the simple tutorial and copy and paste code was perfect for me. thanks so much for sharing
You're welcome, Vanisha, and thank you for letting me know that it worked for you!
Thank you so much, this has made things so much more easier on my spread sheet 🙏🏽👍🏽👍🏽👍🏽 Cheers
5 years ago and excellent video, made my day!
Thanks so much for this video and the one click version also. Very helpful!
Thanks so much. This example was perfect for my case.
Is it possible to activate the macro using a button, for example F2? So I dont need to use the mouse....
Thanks!!!
Would this work together with the vlookup function? thank you very much for this video.
Thanks for the tip. I have experienced the same issue with DV. Did you have to copy and paste a new combo box in each row? What if I needed to make a large column (like 2000 rows) of data validations using combo box instead of DV list?
Hi, thank you for this. If the data for the combo box list is on another sheet the code does not work. what can I do to make this work? Many thanks!
Thanks for you tutorial. But can I use list name which is on other sheet in data validation? because I tried it by using list name in data validation but it just works only with data validation but not autocomplete.
Thanks a lot, Mdm. Worked Perfectly, and saved a lot of time.
Hi
I have followed the instructions in "Data Validation Combo Box using Named Ranges" and it works perfectly - but only if the data verification is not dependent - that is, in the data validation definition, source is "=SomeRange".
My drop down list is dependent - done using your examples - that is, in the data validation definition, source is "=INDIRECT(a-cell-reference)"
How do I adapt the macro to work with and indirect source.
Thanks
Thanks, this helps a lot to my Inventory Management.
I downloaded your single click file but am having trouble making it work as you did. The combobox is visible at all times. The combo box will not pop into my input cell, and so doesn't pick up my validation range. What would you advise I am doing wrong?
This was very cool! I built it from your written instructions on your website url above and it worked like magic!
Hi, I used this and it worked great. However, when I try to protect the sheet, which I need to, the function is no longer available. Is there a way round this?
Thanks,
James
Hi, Is there any chans you can get it to work with merged cells? I merge two cell but then dubbel click not working any more.
Regards
Patric
hi, Thank you for the video. i am facing some issues with this. I have copied and pasted the codes, and tried double click the data validation cell, it is showing that there is a combo on the cell but it is not giving me any list in that combo box. am i missing any steps here?
please advice
Thanks
I would like to use this for multiple words, rather than just single word selections. Is there a special way or would this technique work for both?
Thank you so much! Your web And video really help!
Once we chose the data what we need, is it possible to edit the data in same cell without modifying the original data?
This is exactly what I've been looking for. Thanks
Thanks, Mark!
hey, i just wanna say, thank you so much, you help me out :) this is great tutorial
Brilliant! This was extremely helpful. Thank you!
You're welcome, Archana! Thanks for letting me know that it was helpful.
Worked for me, thank you!!
Hi, Great video...I think this is just what I'm looking for...one question, one problem: 1. in order to activate the autofill feature do I have to utilize the mouse or can I do it all from the keyboard? 2. I'm using Mac 2011 v 14.1.3 and when I open your sample I get a series of compiler errors. 'Method or data member not found'...any ideas on that issue? If you want screen shots of the error windows that pop up let me know. If so I'll contact you thru your website? Thanks! Mike
Can you make the combo box activate when in the cell at all? Not just when double clicking? (For instance pressing enter from the cell above it.
Major Kudos! Great feature!
Would like to create this on all of my excel rows is this possible?
Me too. can't find the example that you mentioned where you can use only the keyboard to input data. When working with big sheets is a pain in the a** to double click each cell. Could you post a link? Thanks
I'm using an INDIRECT function to pull the data for my data validation list from another workbook. I've entered your code but right now the combo box is blank. Is there a way to modify your code to get this to work? Thanks.
Hey there, do you have a version that works on a merged cell?
Hi there, I used your code for merged cells to create the active x combo box, However i need it to be single click and i have 5 cells that it needs to populate on. it only is populating on 3. how do i change the code for single click and to add more cell options?
Is there auto filtering based on the text typed? Say I have the phrase 'and' in multiple different text. I want to be able to grab anything with 'and' only. Thanks.
That is EXCEcLy what I am looking for. Greetings from Germany!
Thank you so much for this video! Its really helpful. But I have a MAC and Im not sure how to implement this. I checkout your website and the link to Prof. Townshed but that didn't quite help me. I would really appreciate your help. Thanks!
Thank you - very useful!!
Could this be done so you don't have to use the mouse? Data entry is most efficient if you can stick with the keyboard. Thanks
This is great!! However, it does not seem to want to work with the use of "indirect" in the Data Validation. Do you know an way to get it to work with the indirect command?
Is there a way to do this in Google Sheets?
Dear Debra,
I have followed your example and managed to apply it to my excel file. However the auto complete feature is not working anymore on conditional data validation list. (eg : =IF($D$6="Entity1",list1,list2) )
Any thought?
Thanks in advance.
Regards
Very cool! Thanks!
I hope you can help me with this question: I have 2 Sheets, Sheet 1 contains data that I would like to auto populate in Sheet 2. Sheet 2 has data ithat is not relevant to Sheet 1 and is manually inputted. I would for Sheet 1 to auto populate data in Sheet ONLY if there is a blank row for it.
Thank you very much! Just made an inventory list so much easier to locate item!
You're welcome, Thad! Thanks for letting me know that it helped you
anyway to make mouse scroll wheel work ?
How do you get that one combo box to work with several different cells?
@David Moore, make sure that you enable macros. Also, on the Developer tab, make sure that the Design Mode is turned off.
I am having the same problem. I managed to get the combo box there, but it does not go away and it does not pop into the input cell. I have "enable all macros" set and also checked "trust access to the VBA project object model". I also have ensured design mode is turned off. Even your sample file is not funcioning the way it does in your video. any recommendations?
I have a page named "Data" where I am keeping my lists. I have a template page that I will use to create several new pages. When I try to copy/paste in a new page, the Combo/Auto Complete does not function. Plus, it seems to disable the original template from working as well. Any suggestions on a fix?
very helpful Debra
Where on your website kan I find this example?
Very well made
Could you please add a link, can't find that example. Thank you
One question, if we have a list box (data validation) it will be in a cell, then in VBA we can address that particular cell value ( D2 here).
i.e. =IFERROR(MATCH(D2.Value,CustID,0)+3,"")
How can this be done with Combo Box?
Basically I have Cust_ID & Cust_Name and I would like to get the Cust_ID or its row when I select a customer's name from a Combo Box?
No, there is just one combo box for the worksheet, and it is positioned over the active cell, if it has a data validation list.
Hello I also have 2010. The autocomplete works in the sample file - but it doesn't work in mine. I have checked the Properties in both spreadsheets and they are the same - even the font and row size...for kicks. I even took the code directly from the sample file...I cannot get it to autocomplete. The combo box does show in every cell...and the original ComboBox does hide - but the rows just don't autocomplete. Have you figured out why it would do that in my file, but not yours? [I see several people have also asked, but I haven't seen a response to this & like the others I can only pull up the ComboBox properties as an ActiveX ComboBox]
Thank your for this video and the sample file. This will save me and my co-worker a lot of time. I'd like to compensate you somehow, since we are using it in a commercial application. Is there a way to do so?
What if I got a DATA validation list on a different sheet?? please help
Hello, I really appreciate your videos! I was able to do a data validation combo box for my filtered list, and once I click on the button I was able to see three suggestions from my filter search... I did set up the properties to list 30, it just doesn't take. Is there something more that I should be doing with 365?
The combo box will show the data validation list from the active cell. How many items are in the list when you click the drop down arrow in the data validation cell (not the combo box). You might need to adjust your data validation formula, to include all the items you need in your list.
Unable to Understand that in column B and C..... Are they all combo box...?
Thank you very much.
May this happen with single click ?
Yes, there is a sample file that works when you click on a cell, and you can get it on my website: www.contextures.com/xlDataVal14.html
Thank you very much :-)
Dear Debra,
I've tried to follow all the steps you've explained bt it does not seem to work!
I tried to do it from scratch following the instructions on your website, it did not work!
I tried to copy paste the code and the combo box, it did not work!
I tried to replace data into your sample file, it did not work!
I'm not sure where I'm going wrong!
Please assist!
Wow, it's great. You solve my problem in spreadsheet
Thank you! I"m glad it helped solve your problem
I tried to follow your instructions on your web page step by step. After inserting the code, and trying to return to the Excel Sheet I get this: "Compile error: User-defined type not defined". How to proceed?
I got the same error. Did you ever get this resolved?
Hi - I've noticed a number of people asking this, but I couldn't find the answer - how do I use this autocomplete with dependent data validation cells, i.e. =INDIRECT(....)?
you are a star thank you so much
You're welcome, Mahmoud, and thanks for your comment!
Is this method also applicable for drop down list whose source is in different workbook ??
You said "There is another example on my website, and it shows the combo box as soon as the cell is selected. That version should work with the keyboard only."
Would you please paste the link for the same over here
I have the example working fine for my projects, which is great. Could you please put a link to the example that shows the combo box as soon as the cell is selected as this would be a great solution for me. Thanks again for the above!!
WINDOWS VISUAL BASICS FOR APPLICATION KEEPS POPPING UP, HOW TO RESOLVE?
Anyone knows a similar solution but supporting Undo?
i want something like if I have a word in drop down and that word contains a whole lot of data in it, and if i click on that word the whole data in it should appear in the tables set below .... Not able to get how to do... Any help?? :)
And this video also helped me a lot...........!!!!!!!!11
This is Good, but there is an issue it only starts matching from start of the list. I need to match it from second word. i.e. "Milk Pak" it shows when we type Milk but dont show when we type Pak... any help plz
How can I get it to work with dependent lists (example =indirect(A1.... ) ??
anyone answer this question please
the answer is in the link above
@@zachalgren4677 no way around coding right?
your website doesnt work. how can i get the code ??
Can we make drop-down list which will not take other entries out from drop-down list
This video shows how you can add multiple items in a cell, using a drop down list: ruclips.net/video/8x6YUsl7Ld4/видео.html
The Dropdown list does not work in Combo box when i double Click no list shows on it. i also use "=INDIRECT"
Will the combo box work with a table? I have a table in which users type a number in column A to create a new row. The new row then has dropdowns in several columns of the table. Will this combo box solution (what I am looking for here is the pre-fill selections from the list upon typing the start of an item on the list) work with the table that I am using?
Yes it work with tables, just have to validate using directions as normaly do without a table
This is a wonderful macro and has definitely made it easier to access longer lists. I just need to modify a couple of things in the code to do the following and can't get my head around it. I need the combobox to appear when I press ENTER at the required cell instead of double clicking or appearing automatically.
Also, I need to accept and close the combobox by pressing ENTER again or press ESC to close it without accepting the change. And it should stay in the current cell instead of moving down in all situations.
I really need this. Did you find an answer?
I don't think I heard from anyone on the forum but yes I did find a workaround. It's really been a while. Will have to search for the exact file.
+Sphinx Factor Don't spend too much time on it, I've found a suboptimal solution. But a solution like you described above would be optimal.
Hi, I am creating activexcontrol box but I am having data in string seprated by comma. Like "Apple, Banana, Cat, Dog, Elephant". Could you please make a video on that or share the link if it is already available as soon as possible.
@Contextures Inc. could you please also look at my problem. Please
It worked 💖
Thanks, Kaye Ann, that's great to hear!
Firstly, thanks for the vid. I'm working with Excel 2010, and I can only choose Design Mode after clicking Insert > ActiveX Controls: Combo Box. Perhaps I'm doing something wrong. Also, did you put the ComboBox on the lists or sample page? The outcome that I am having after this is that the box appears but it doesn't autocomplete. I would love to be able to use this feature.
your list is not on the same sheet
Dear Debra,
I liked your video instruction and its exactly what i would need for a datasheet at work.
However when i follow your instructions as well as when i use ur templates, the hidden combo-box never appears as it does in your video. Consequently it does not autocomplete as it should.
Is the template on the homepage the same as in the video?
Where can the problem be that it doesnt autocomplte?
I appreacita your help, thanks a lot,
Sebatain
Sebastian Schuhmann Make sure that macros are enabled -- here are instructions on my website:
www.contextures.com/xlvba01.html#security
hi, thank you so much for the video. but I have a little problem. How do I allow the combobox to function when the sheet is protected? It works fine on an unprotected sheet. I try to unlock the cell that associated with my combobox but it does not work.
Thank you in advance!
When you protect the sheet, add a check mark to "Edit Objects", and see if that fixes the problem
Thank you for the quick response, I already tried that and it works but the problem is when I closed or go to another sheets the combobox is not working properly. All my sheets are protected with VBA code. so that the other users will not mess up the formula i make. Is there any VBA code protection for that? Thank you in advance again?
Record a macro while you protect the sheet and choose the protection options. That will show you what to include in the VBA. For example, DrawingObjects:=False
its working, thank you very much and God bless!!!
You're welcome! Thanks for letting me know that it's working now.
how does this thing connect with my list?
THANK YOU
You're welcome, Hrithik, and thanks for your comment.
It would be nice how you tell someone to create combo box for combo box
Thank you so much for this. It's so useful ! I noticed that on the files that I use this, I can only use "Undo" once end then it's greyed out. Does anybody else experimented this ?
You're welcome, Christine, and any Excel macro that makes a change to a worksheet, also wipes out the Undo stack, unfortunately!
@@contextures Thank you ! I didn’t know that.
superb
Works a treat, But how can I get the changes to to be sustained when closing an opening the document?
Nik, the changes should be stored in the file, as long as you save it when closing.
Hi there, great tutorial, it really helped me out. However I have to use the mouse to get to the next cell when entering data. Is the a formula which allows me to hit tab or enter, and it then moves to the next cell?
Steve, there is code in the sample file that moves to the next cell when you press Tab or Enter. You can see it on the tutorial page too, in this section: www.contextures.com/xlDataVal11.html#numbers
Yep, already tried that, and no luck. I am using Excel 2016. Would that make a difference?
I'm using Excel 2016, and when I press Enter, the cell below is selected. What happens when you try that in the sample file?
Nothing. I have to use the mouse to get to the next cell. I have most of the worksheet locked off, with only specific cells unlocked for data entry.
When you test in the sample file downloaded from my site, without any changes, does the Tab/Enter take you to a different cell?
This is a wonderful trick and great tutorial. Is there an alternative to a double click? Would prefer not to have to my hands off the keyboard when going through the lists. Thanks!
I see you do have alternative code for single click / activation - that's really great - thanks!
Thank you, Maxine! There is another version that runs when the cell is selected: www.contextures.com/xlDataVal14.html
Guys I didnt get it :D same question here
Do i Need to Change the Code? Or the properties?
Thanks :S
Got it :)
is it possible to do the same thing with excel mac?
The ActiveX controls aren't available on the Mac, so you would have to use the Form controls instead. Dr. Lee Townsend, at University of Hartford shows how to use combo boxes on the Mac:
uhaweb.hartford.edu/ltownsend/excel.html#FormControlComboBox
Big thanks, now I can help my brother, and get some pennies :D
I can't find the example that you mentioned where you can use only the keyboard to input data. When working with big sheets is a pain in the a** to double click each cell. Could you post a link? Thanks
For the example that works when you click on a cell, go to this page:
www.contextures.com/xlDataVal14.html
Thanks for your response, but I was trying to find out if there is a way to use only the keyboard to input data and move around different cells
nietzc pastebin.com/3TvLu5Wt here it is, for more info see my comment below Autocomplete Entries With Excel Drop Down List
Hello. I get a syntax error in "Private Sub TempCombo_LostFocus()". Any suggestions please? Thank you in advance.
I get the same error, did you ever figure it out?
Any luck same issue for me :(
Hey, that " library/aa243025%28v=vs.60%29.aspx " part that gets highlighted in red text when the code runs is not formatted properly in the sample code.
Either delete the line, add a ' in front of it (' library/aa243025%28v=vs.60%29.aspx) or format it correctly such that it looks like 'msdn.microsoft.com/en-us/library/aa243025%28v=vs.60%29.aspx
The line is entirely not necessary though either way.