Solved: Autocomplete cells

[anovice]

Hi again !

Is it possible to autocomplete the first few letters that I type and match an existing entry in another worksheet ?

E.g. Sheet 1 Col A Row 1 = Ano which would automatically complete the text from Sheet 2 Col A Row 1 = Another

Thank you

[nst1107]

See this KB entry. http://www.vbaexpress.com/kb/getarticle.php?kb_id=244

[anovice]

Thank you nst1107 !

Problem solved.

[ChristineJ]

I have a list of 40 unique names in column A, a1:a40, on Sheet1.

I want cells in columns B and D on Sheet2 to auto-complete as the user types based on the choices in column A on Sheet 1.

I do not want to use a dropdown list because I do not want users to see what the choices of names are.

The kb entry below is EXACTLY what I need, but for some reason the sample spreadsheet attached to it is not working for me, nor is the code that is provided. (This is my first post so it will not let me post a link - the URL is below.) Any help would be appreciated! Great forum!

[mdmackillop]

Hi Christine,
Welcome to VBAX.
I had no problem with the KB Item. Check your Macro Security settings.

In any case, I found "Enter" after each letter a nuisance. Try this simple Userform alternative.

Option Explicit
Option Compare Text
Private Sub TextBox1_Change()
Dim i As Long
Dim cel As Range
Dim MyStr As String
i = 0
For Each cel In Range("Data")
   If cel Like TextBox1 & "*" Then
      i = i + 1
      MyStr = cel
   End If
Next
Label1.Caption = i
If i = 1 Then
   ActiveCell = MyStr
   Unload UserForm1
End If
If i = 0 Then MsgBox "Name not found"
End Sub
 
Private Sub CommandButton1_Click()
ActiveCell = Application.WorksheetFunction.Proper(TextBox1)
Unload UserForm1
End Sub

[ChristineJ]

Excellent! Thanks for the help and the quick response. This is a much improved version. CJ