BET: Microsoft Excel Data Validation - Drop-Down List

Private Sub cmbAutoCompleteOn_Click()
Call KeyEventOn
End Sub

Private Sub cmbAutoCompleteOff_Click()
Call KeyEventOff
End Sub

7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sText As String
Dim sList As String
Dim objCell As Range

   If TypeName(Selection) <> "Range" Then Exit Sub
   If Target.Cells.Count > 1 Then Exit Sub

   If Application.Intersect(Target, Range(gsDropDownDisplayRange)) Is Nothing Then
      Target.Validation.Delete
      Exit Sub
   End If

   sText = Target.Value
   With Selection.Validation
      If Len(sText) = 0 Then
         .Delete
         .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, _
                 Formula1:="=" & gsNamedRange
      Else
         For Each objCell In Range(gsNamedRange)
             If InStr(1, objCell.Value, sText, vbTextCompare) = 1 Then
                 sList = sList & objCell.Value & Chr(&H2C)
             End If
         Next

         Selection.Value = sText
         .Delete
         If Len(sList) = 0 Then
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, _
                 Formula1:="=" & gsNamedRange
         Else
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, _
                 Formula1:="A,B,C"
            .Delete
            .Add Type:=xlValidateList, _
                 AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, _
                 Formula1:="=" & gsNamedRangeReduced
         End If
      End If
   End With

End Sub

Public Const gsWorksheetName As String = "Sheet1"
Public Const gsDropDownDisplayRange As String = "B3:B10"
Public Const gsTemporaryListFirstCell As String = "E3"
Public Const gsNamedRange As String = "Countries"
Public Const gsNamedRangeReduced As String = "ReducedCountries"

Public Sub KeyEventOn()
Dim icount As Integer
    For icount = 65 To 90
        Application.OnKey "{" & icount & "}", "'MyValidation """ & icount & """'"
    Next
End Sub

Public Sub KeyEventOff()
Dim icount As Integer
    For icount = 64 To 90
        Application.OnKey "{" & icount & "}"
    Next
End Sub

74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98

Public Sub MyValidation(ByVal KeyCode As Long)
Dim sText As String
Dim sList As String

    If TypeName(Selection) <> "Range" Then Exit Sub

    sText = Selection.Value & Chr(KeyCode)
    sList = CreateReducedNamedRange(sText)

    Selection.Value = sText
    With Selection.Validation
       .Delete
       If Len(sList) > 0 Then
         .Add Type:=xlValidateList, _
              AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, _
              Formula1:="A,B,C"
         .Delete
         .Add Type:=xlValidateList, _
              AlertStyle:=xlValidAlertStop, _
              Operator:=xlBetween, _
              Formula1:="=" & gsNamedRangeReduced
       End If
    End With
End Sub

99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119

Public Function CreateReducedNamedRange(ByVal sChar As String) As String
Dim objCell As Range
Dim irowcount As Integer

   irowcount = 0
   With Worksheets(gsWorksheetName)
      .Range(Range(gsTemporaryListFirstCell), _
             Range(gsTemporaryListFirstCell).Offset(200, 0)).Clear

      For Each objCell In Range(gsNamedRange)
         If Left(objCell.Value, 1) = sChar Then
            .Range(gsTemporaryListFirstCell).Offset(irowcount, 0).Value = objCell.Value

            CreateReducedNamedRange = CreateReducedNamedRange & objCell.Value
            irowcount = irowcount + 1
         End If
      Next objCell
      .Range(Range(gsTemporaryListFirstCell), _
             Range(gsTemporaryListFirstCell).Offset(irowcount - 1, 0)).Name = gsNamedRangeReduced
   End With
End Function

Leading the way in Microsoft Office Development

Step 1 - How to create an AutoComplete Drop-Down

There may be times when you want to use a drop-down list box to make your selection but do not want to scroll down a long list to find the item you want.

This example shows you how your drop-down list can be automatically filtered to only show items that begin with a particular letter.

Many thanks to Colo (Masaru Kaji) for this solution.

Step 2 - Code Module - Sheet 1

Step 3 - Standard Code Module