I was wondering if there is a way to AutoComplete a combo box without having to write some kind of VBA macro to do it. I am using the Excel in Office 365 updated to Sept 2017. I have a drop down list with about 20 alpha names in it.
Drop Down Lists
Posted 03 January 2018 - 03:25 PM
When you say AutoComplete, I assume that you are referring to the behavior where controls 'guess' what you are going to enter based upon your input. It isn't possible to do this using Excel's ComboBox Form Control, but this feature is available with the more advanced ActiveX Control version.
The Form Control version of ComboBox is simplified to make it easier to use for non-developers, while the ActiveX Control version allows its full feature set to be used, but requires more effort to set up correctly.
You can see how to use the Form Control version in: Expert Skills Lesson 9-2 Add a combo box control to an Excel worksheet form.
Here's how you can do the same thing with the ActiveX Control and enable the AutoComplete feature:
- Add a ComboBox ActiveX control from the Developer > Controls > Insert menu (as shown in lesson 9-2).
- Click Developer > Controls > Properties to display the Properties window if it isn't displayed already.
- In the Properties window, set the ListFillRange property to the cell reference that contains your names (such as A1:A20).
- In the Properties window, set the MatchEntry property to 0 - fmMatchEntryFirstLetter.
This will cause your ComboBox to allow the user to type text into it and automatically try to guess the item that the user wants.
- To exit Design Mode, click Developer > Controls > Design Mode.
You can now test your ComboBox.
I have attached an example workbook showing this in practice:
ComboBoxAutoFill.xlsx 13.49KB 43 downloads
I hope this is the solution that you were looking for, but please feel free to reply if you need any more help with this.
Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.
Posted 04 January 2018 - 06:50 PM
You are correct in that I was referring to Excel filling the rest of a word that I entered.
I may have been incorrect in calling it a combo box.
What I have is a column that refers to a list of names I have entered in a column outside the columns I am working with but on the same worksheet. EG I am using columns A thru T and the list is out in column Z.
Column C is the column I am using. When I click on a cell in column C I get a tiny square on the outside of the cell with a downward arrow. If you click the down arrow you get a list of the names.
Now that the table is finished, I have noticed that when I enter something in a cell in column C it is now doing what I had wanted in the first place. and I didn't do anything.
So I hope I didn't waste anyone's time on this matter as I am new to Excel and I am teaching myself using the Basic, Essential and Expert books from this site.
I would have posted a picture of what I am doing but I haven't figured out yet how to paste a picture inline on this reply.
Thanks for your help and patience!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users