Auto Complete

[p9j123]

I have a list of words in Sheet2 column A.

I have a dropdown list using data validation in Sheet1[R9], I would need help in making R9 auto complete when I type some value that matches the list without me using the mouse to use the dropdown or typing the entire word.

 

[jasonb75]

You would need to move the source list so that it is above R9 AutoComplete Typing in an Excel Data Validation List Without Macros

 

[Michael M]

You willneed to use a Combo box instead of a Dropdown list.
Have a read here for complete instructions on how to do it ....including the code required

How to autocomplete when typing in Excel drop down list?

ExtendOffice provides a comprehensive range of professional software solutions, enhancing productivity and efficiency for businesses worldwide.

www.extendoffice.com

 

[p9j123]

Thanks Michael M.

It's a good start, how can I set the source of the list?

 

[Michael M]

change the listfill range

With xCombox
        .ListFillRange = "" 'change to the source data
        .LinkedCell = ""
        .Visible = False
    End With

 

[p9j123]

Thanks, I am having compile error saying "Cancel" variable not defined

If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
      Cancel  = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub

 

[jasonb75]

Where did cancel come from?

There is no cancel in the code on the page that Michael pointed you to (cancel doesn't belong in a worksheet change event).

 

[Akuini]

Another way:
A searchable combobox without using data validation, and it can be applied to multiple cells, you can find an example here:

Best way to create a searchable drop-down list with auto-complete functionality to cells in a column?

Hi Folks, I have a time-sheet workbook with two worksheets (ws). In sheet 1 (the time-sheet ws), Column A is "Employee". Time-sheet worksheet. Employee Start time Finish time Hours worked Joe Blogs Jane Doe I wish to be able to start typing a different employee on each row...

www.mrexcel.com