predictive text in List dropdown

[VisualBasic]

Hi all,

I have a list of locations, I then clicked another cell went to Data tab and selected Data Validation, and then clicked under "Allow" the List option and for source I put the range of my locations.

So I now have a list dropdown of locations.

What I need to do is add predicitive text to this, so when the user selects L, it for example shows london. This works when u use the filter option, but I need it to work using List Dropdown, is this possible, >???? maybe via VBA code.

 

[VoG]

You could use a combo box http://www.contextures.com/xlDataVal14.html

 

[VisualBasic]

Hi, this doesnt meet my requirement, cause if i type in the combo box feb..i would want it to auto predict february...

please help me

 

[VisualBasic]

sorry it does work!!

 

[VoG]

As explained in the article you have to enable AutoComplete.

 

[Danmallon]

Hi, I am looking for something similar to this and it works perfectly if the 'data validation list' equals an array that's on the same worksheet. Although, I have some lists on that worksheet that equal arrays from different worksheets within the same workbook. Is there a code that you know of that can be entered in so that it changes all the 'Data Validation Lists' in the workbook so that they can become predictive texts?

I am also not too sure if this works with merged cells, is there a way to make it work for merged cells too?

Your expert advice would be greatly appreciated

-Dan

 

[CDNWolf]

Hi, this doesnt meet my requirement, cause if i type in the combo box feb..i would want it to auto predict february...

please help me

I am looking for a similar solution. But to clarify the solution above if you type in "m" can't the list truncate down to March and May only?

In my case I want to use it with part numbers and there are about 1000+ items which are similar codes ie: PRT147, PRT155, PRT 987... If I type PRT I'd like it to give me my options or if I type PRT1 again it would isolate those with PRT1 as part of the part number.

 

[Roderick_E]

I know this is very old but here's my vba solution to auto populate a cell with a data validation if the user types on the beginning string of one of the elements in the list. Will require turning OFF the Error Alert.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strarray As String
Dim strunbound() As String
Dim i As Long
On Error Resume Next
If Target.SpecialCells(xlCellTypeSameValidation).Cells.Count <> "" Then
strarray = Target.Validation.Formula1 'or any string with unique dilimiter
strunbound = Split(strarray, ",") 'replace vbCrLf with any unique delimiter such as ","
For i = LBound(strunbound) To UBound(strunbound)
If InStr(strunbound(i), Target) > 0 Then
Target.Value = strunbound(i)
Exit Sub
End If
Next i
End If
Resume Next
End Sub

 

[CDNWolf]

Roderick,

Thanks for the post, I'll try it out.


Thanks again