autocomplete list definition
Posted by Paul Ohlerking on January 22, 2002 7:24 AM
How can I define an auto complete list? I have a list of about 100 items on worksheet2 that I would like to use to fill cells on worksheet1 after typing the first few chacters.
any input is welcom
Posted by Chris D on January 22, 2002 1:36 PM
In my tests (In '97) i think i found out that data validation doesn't accept UDF's, is this correct ?
Anyway, here's what I did.
First, the NoNumbers UDF, to strip any characters out of the string (Can be adapted to include $,%,& and anything else), and second, the MyCondition UDF, which is basically the same formula that has been around, but in VBA.
I assumed the data was gonna be entered in Column A. I enter in ColumnB
=MyCondition(A2) and drag down. 'This could be done directly in Validation, but didn't work, avoiding the extra column...
Now, select Column A, go to Data, Validation and put as condition
=B2
That worked..
Juan Pablo G.
'Code here...
Function NoNumbers(Rng As Range) As String
Dim T As String
Dim i As Byte
T = Rng.Value
For i = 0 To 9
T = Application.Substitute(T, i, "")
Next i
NoNumbers = T
End Function
Function MyCondition(Rng As Range) As Boolean
MyCondition = (Len(Rng) > 2) * (Len(Rng) < 7) * (Len(Rng) = Len(NoNumbers(Rng))) * (InStr(1, Rng, UCase(Rng), 0) = 1)
End Function
