Autocomplete in a cell

[pir81]

Dear all,

I have two worksheets. One is the input, the other holds a list of 100s of names (ultimatley it will be two separate work books because the input workbook will be started from zero each year, but the list sheet will grow... but step after step.. as I assume that debuging is easier within the same workbook first and I don't have the skills and understanding to skip this step with confidence).

I know that I could copy/link all the names into the cells above my input cells and hide them, but I don't think that is a very clean way.

Googeling a solution I found this
http://www.mrexcel.com/forum/showthread.php?t=31107
for a text field and tried to adapt it to a cell. But for some reason I am not able to get it to run.

Can you please help me understand where I am wrong?

Option Explicit
Dim oRange As Range
Dim iCharCount As Integer
Dim strAuto As String
Dim sTemp As String

Private Sub Worksheet_Change(ByVal Target As Range)
' to which cell changes to react (this works fine)
    If Not Intersect(Target, Range("B2:C10000")) Is Nothing Then ' if intersect is nothing it would be out of range
    If Range("B10000").End(xlUp).Row = Range("C10000").End(xlUp).Row Then
        Range("F1").Formula = Range("B10000").End(xlUp).Row
    End If
    ' here starts the autocomplete
    Set oRange = Worksheets("list").Range("a2").End(xlUp).Offset(999, 0) 'select from A2 to A1000
    oRange.Value = Target.Text
    strAuto = oRange.AutoComplete(Target.Text)
    If Len(strAuto) > 0 Then
        With Target
            sTemp = .Text
            .Text = strAuto
            .SelStart = Len(sTemp)
            .SelLength = Len(strAuto)
        End With
    End If
    MsgBox Target.Text
    oRange.ClearContents
    End If
End Sub

Any help is very much appreciated.

[Bob Phillips]

Post the workbook, so we can see what we are working with.

[pir81]

Here is the work book.

I also simplified the first few lines, so only the relevant code is in the workbook.

Thank you.

[pir81]

Does anyone know how to tackle this? I think the problem is that there is no continuous event catcher for every single cell key stroke. Could that be true?

[Aflatoon]

Yes - the change event for a sheet is not analagous to the change event for a textbox. I would suggest that you use a combobox instead, as was suggested early in that MrExcel thread.

[pir81]

Thanks Aflatoon