SOLVED

Autocomplete in a data validation cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2391862%22%20slang%3D%22en-US%22%3EAutocomplete%20in%20a%20data%20validation%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391862%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20Office%20365%20Excel%20in%20Win10%20desktop%3C%2FP%3E%3CP%3EMade%20a%20form%20with%20a%20data%20validation%20column%20for%20products%20from%20a%20separate%20sheet%20in%20my%20workbook%3C%2FP%3E%3CP%3EWatched%20the%20Contextures.com%20video%20to%20make%20it%20possible%20to%20autocomplete%20the%20cell%20by%20typing%20in%20the%20first%20letter%20from%20the%20list%20of%20100's%20of%20products%2C%20by%20adding%20a%20Combo%20box%20and%20the%20code%20they%20provide%20but%20it%20doesn't%20work.%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20probably%2099%25%20of%20people%20using%20a%20form%20with%20hundreds%20of%20items%20to%20choose%20from%20would%20want%20to%20have%20autocomplete%2C%20you%20would%20think%20MS%20would%20include%20this%20option%20the%20Data%20Validation%20pop-up%20box%2C%20but%20no%2C%20somehow%20you%20are%20suppose%20to%20figure%20out%20this%20mundane%20task%20by%20watching%20100's%20of%20videos%20and%20thousands%20of%20threads%20that%20either%20do%20not%20apply%20to%20Office%20365%20or%20are%20so%20convoluted%20only%20a%20programmer%20could%20figure%20out.%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2391862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2391951%22%20slang%3D%22en-US%22%3ERe%3A%20Autocomplete%20in%20a%20data%20validation%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2391951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1064855%22%20target%3D%22_blank%22%3E%40ladyfairfax6203%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20attach%20a%20copy%20of%20your%20workbook%2C%20someone%20may%20be%20able%20to%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399737%22%20slang%3D%22en-US%22%3ERe%3A%20Autocomplete%20in%20a%20data%20validation%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%20here's%20the%20sheet%20with%20the%20VB%20code%20pasted%20in%20from%20contextures.%26nbsp%3B%20Seems%20crazy%20that%20this%20isn't%20automatically%20an%20option%20when%20you%20create%20a%20data%20validation%20list%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2399741%22%20slang%3D%22en-US%22%3ERe%3A%20Autocomplete%20in%20a%20data%20validation%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2399741%22%20slang%3D%22en-US%22%3EAlso%2C%20I%20usually%20use%20Access%20for%20this%20type%20of%20stuff%2C%20but%20in%20this%20case%20it%20has%20to%20be%20Excel%20because%20others%20will%20need%20to%20be%20able%20use%20it%20on%20their%20phones%20and%20that's%20not%20an%20option%20with%20Access.%20Quickbooks%20Online%20is%20useless%20for%20this%20type%20of%20business%2C%20so%20that'%20why%20I%20am%20trying%20to%20figure%20this%20out%20in%20Excel%3C%2FLINGO-BODY%3E
New Contributor

Using Office 365 Excel in Win10 desktop

Made a form with a data validation column for products from a separate sheet in my workbook

Watched the Contextures.com video to make it possible to autocomplete the cell by typing in the first letter from the list of 100's of products, by adding a Combo box and the code they provide but it doesn't work. 

Since probably 99% of people using a form with hundreds of items to choose from would want to have autocomplete, you would think MS would include this option the Data Validation pop-up box, but no, somehow you are suppose to figure out this mundane task by watching 100's of videos and thousands of threads that either do not apply to Office 365 or are so convoluted only a programmer could figure out.

Any help would be appreciated.

5 Replies

@ladyfairfax6203 

If you attach a copy of your workbook, someone may be able to help you.

@Hans Vogelaar  here's the sheet with the VB code pasted in from contextures.  Seems crazy that this isn't automatically an option when you create a data validation list

Also, I usually use Access for this type of stuff, but in this case it has to be Excel because others will need to be able use it on their phones and that's not an option with Access. Quickbooks Online is useless for this type of business, so that' why I am trying to figure this out in Excel
best response confirmed by allyreckerman (Microsoft)
Solution

@ladyfairfax6203 

See the attached version. I simplified the code.

Wow - thank you - this has been driving me nuts - and apparently I am not the only one out there who uses excel for a lot of things but doesn't write code and gets stumped when something so basic isn't built into program. Microsoft ought to pay you the big bucks!