Shorten Data Validation List

[FryGirl]

I have downloaded and used a workbook from Contextures.com which works great for condensing the pick from a DV list. Instead of filtering from the beginning of the word, how can this become a wildcard? Example, in the file, if I only wanted to see names that contained the combination "be"? That would only return Bell and Imber.

FL0024 - Shorten Data Validation List

My thought would be to add wildcards, but evidently this approach is not right.

NamesSort ("*" & "CriteriaLetter" & "*")

Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsSD As Worksheet
Dim wsS As Worksheet
On Error GoTo errHandler
Set wsSD = Sheets("Sales Data")
Set wsS = Sheets("Summary")
Application.EnableEvents = False
Select Case Target.Address
Case wsS.Range("LetterSel").Address
Target.Value = UCase(Target.Value)
wsS.Range("NameSel").ClearContents
NamesSort ("CriteriaLetter")

If Target.Value = "" Then
wsS.Range("ExtractOrders").CurrentRegion.Offset(1, 0).ClearContents
Else
OrderSort ("CriteriaLetter")
End If

Case wsS.Range("NameSel").Address
If Target.Value = "" Then
NamesSort ("CriteriaLetter")
If wsS.Range("LetterSel").Value = "" Then
wsS.Range("ExtractOrders").CurrentRegion.Offset(1, 0).ClearContents
Else
OrderSort ("CriteriaLetter")
End If
Else
OrderSort ("CriteriaName")
End If
Case Else
'do nothing
End Select

exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
MsgBox "Could not filter orders"
Resume exitHandler
End Sub

 

[FryGirl]

Bump. Does anybody have a thought on this?

 

[Akuini]

Maybe this would help (but I've never tried it myself)
searchable-drop-down-list

As an alternative, maybe you're interested in using a searchable combobox (not data validation) that can hide-unhide automatically.
Please check post #2 and the revised code in post #15 in this link:
best-way-to-create-a-searchable-drop-down-list-with-auto-complete-functionality-to-cells-in-a-column

 

[FryGirl]

Thanks Akuini. Both of those were great solutions and surely one of these will work. I'm leaning towards the first link, but will have to see how it fits in with my overall project. Thanks again.

 

[Akuini]

You're welcome, come back anytime if you need help.