PDA

View Full Version : [SOLVED] Solved: Autocomplete cells



anovice
04-10-2009, 06:03 AM
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
04-10-2009, 07:17 AM
See this KB entry. http://www.vbaexpress.com/kb/getarticle.php?kb_id=244

anovice
04-10-2009, 08:17 AM
Thank you nst1107 !

Problem solved.

ChristineJ
09-18-2009, 07:04 PM
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
09-19-2009, 05:12 AM
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
09-19-2009, 07:19 AM
Excellent! Thanks for the help and the quick response. This is a much improved version. CJ