VBA TIPS
Something like the Auto Complete function

This may be interesting.

All right. Let's create a new Named Range named MyList then run procedure named KeyEventOn. This procedure assigns the procedure named MyValidation to the A to Z keys.

Next, select the A1 cell and press A key. You can see some words starting with the letter A in the Validation List.

Please don't forget to run KeyEventOff after using this procedure.



Example

Place the following in a standard module.

Option Explicit

Dim i As Long

Sub KeyEventOn()
    For i = 65 To 90
        Application.OnKey "{" & i & "}", "'MyValidation """ & i & """'"
    Next
End Sub
Sub KeyEventOff()
    For i = 64 To 90
        Application.OnKey "{" & i & "}"
    Next
End Sub
Sub MyValidation(ByVal KeyCode As Long)
    Dim strText As String, strList As String
    If Not TypeOf Selection Is Range Then Exit Sub

    strText = Selection.Value & Chr(KeyCode)
    strList = MakeArr(strText)
    Selection.Value = strText
    If strList = "False" Then
        Selection.Validation.Delete
    Else
        With Selection.Validation
            .Delete
            .Add 3, 1, 1, Formula1:=strList
            .IgnoreBlank = True
            .InCellDropdown = True
            .ShowInput = True
        End With
    End If
End Sub

Function MakeArr(ByVal strChr As StringAs String
    Dim a As Variant
    a = [MyList].Value
    For i = LBound(a) To UBound(a)
        If InStr(1, a(i, 1), strChr, vbTextCompare) = 1 Then
            MakeArr = MakeArr & a(i, 1) & Chr(&H2C)
        End If
    Next
    If MakeArr <> "" Then
        MakeArr = Left(MakeArr, Len(MakeArr) - 1)
    Else
        MakeArr = "False"
    End If
End Function


| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo