Data Validation Autocomplete

Question

I have a data validation box coded with VBA (please see below.) I have done loads of reading and cannot find a way to do autocomplete when someone types in the data validation cell.

Most people suggest a combobox, but I have not found a way to place a combo box inside a specific cell. I want it so that if someone tries to copy the row the combobox resides in, they can then paste another row with another combobox.

With data validation, this already is possible, but it does not have the autocomplete ability.

Any help is appreciated!

Sub DataValidation()

Dim WS As Worksheet
Dim WS2 As Worksheet
Dim Range1 As Range, Range2 As Range

Set WS = ThisWorkbook.Worksheets("Report")
Set WS2 = ThisWorkbook.Worksheets("MachineList")

Set Range1 = WS.Range("A7") 'This is the cell the data validation is placed in.
Set Range2 = WS2.Range("A2:A64") 'This will change if the Machine List gains or loses machines.

    With Range1.Validation
        .Delete
        .Add Type:=xlValidateList, _
            Formula1:="='" & WS2.Name & "'!" & Range2.Address
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Machine Name"
        .ErrorTitle = "ERROR: Invalid Machine"
        .InputMessage = "Please enter or select a machine..."
        .ErrorMessage = "You have entered an invalid machine. Please try again."
        .ShowInput = True
        .ShowError = True
    End With

End Sub

Answer 1

While there is no perfect solution, here is one I found that was easy to implement and edit:

http://www.contextures.com/xlDataVal14.html#works (Single cell click/selection) http://www.contextures.com/xlDataVal11.html#works (Double cell click)

Basically, when a user clicks on a data validation cell, this code places a combobox directly over it, (that fills using the same formula as the data validation.) This way the user can still have autocomplete, but doesnt have to worry about the combobox moving if the cells change in any way.