Autocomplete drop down list/autofill drop down list - Solution Found

[bobby_smith]

This is not a question but rather some information that I wanted to share.

I was searching for several months to find option that allows you to autocomplete a data validation drop down list. Similar to when you type something in google and it autocomplete or provide suggestions.

Here is an image of what I'm referring to http://imgur.com/a/YU3Sf
The solutions I came across all referenced the ActiveX option, however this could only be performed on one cell. I needed this feature to work for multiple rows.
I recently came across a FREE addin created by Excel campus that does exactly what I needed.
I simply used the excel data validation list option and then incorporated the addin.
I was really relieved to find this and wanted to share as I've seen numerous people online asking the same question.

The website is https://www.excelcampus.com, and the name of the addin is "List search addin"
https://www.excelcampus.com/vba/search-data-validation-drop-down-lists/

I am in no way affiliated with this website. I just wanted to share this as I'm sure there are many others looking for this.

 

[Michael M]

You can also set it up, without an addin OR ActiveX...See here for full instructions !!

http://www.contextures.com/xlDataVal10.html

 

[Boisgontier]

Hello,

Samples of true intuitive search as Google

http://boisgontierjacques.free.fr/fichiers/DonneesValidation/DVSaisieIntuitiveComboBox.xls
http://boisgontierjacques.free.fr/f...ste_deroulante_intuitive_multi_criteresV2.xls

Boisgontier

 

[bobby_smith]

Hello,

Samples of true intuitive search as Google

http://boisgontierjacques.free.fr/fichiers/DonneesValidation/DVSaisieIntuitiveComboBox.xls
http://boisgontierjacques.free.fr/f...ste_deroulante_intuitive_multi_criteresV2.xls

Boisgontier

Hi Boisgontier,

Thanks for sharing your links. This is exactly what I was looking for. I tried to replicate your code in another spread sheet but I was having some problems.
Can you please tell me to set up the set up the drop down box on my new tab? I tried creating the combo box but it was not working. I'm not able to figure out what step I was missing.

Thanks

 

[Boisgontier]

Property MatchEntry ofComboBox must be frmMatchEntryNone

Other samples

http://boisgontierjacques.free.fr/pages_site/listes_cascade.htm#ListeIntuitiveForm

Boisgontier

 

[bobby_smith]

Thanks Boisgontier I was able to figure it out. Your resources are really awesome.
Thanks Michael M, the link you provided was really useful as it provided some foundation to get to what I wanted.

 

[Jeyd02]

Hey, great Resource! this is just what i was looking for. the only thing that i am having issues is to automatically replace the values of selected items from the list. After i select one of the item on the list i want to automatically replace the value to a linked value from a tablet in sheet 2.

this is a sample code that i kind of want to include in your "DVSaisieintutitivecombobox.xls" VBA code.

[COLOR=#242729][FONT=Consolas]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Dim short As String
    If Not Intersect(Target, Range("A2:A")) Is Nothing Then
        short = Application.VLookup(Target.Value, ActiveWorkBook.Sheets("sheet2").range("A2:B16"), 2, False)
        Application.EnableEvents = False
        Target = short
        Application.EnableEvents = True
    End If </code>[COLOR=#242729][FONT=Consolas]End Sub
[/FONT][/COLOR]

i have a table in sheet2 but in sheet 1 i want to use your vba but i also want the result values to be replace like in the code above upon selection.

I would appreciate any advice, thanks.