autocomplete

[Dantz]

is there a way to create my own autocomplete that the list of strings
are from user inputs? please help me..as of my research I accidentally
got this code but it was really hard to modify esp. the
application.onkey...

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 String) As 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

 

[Jim Cone]

Dantz,

The first Sub below adds items from the selection
to the auto correct list. The second Sub removes
items in the selection from the auto correct list.
Jim Cone
San Francisco, USA

'-----------------------
'To add items to the AutoCorrect list,
'select a two column list where the first column has the words to
'be replaced and the adjoining column has the replacements words.

Sub AddItemsToAutoCorrectList()
Dim rngList As Excel.Range
Dim rngCell As Excel.Range
Set rngList = Selection
Set rngList = rngList.Columns(1).Cells
For Each rngCell In rngList
Application.AutoCorrect.AddReplacement rngCell.Value, rngCell(1, 2).Value
Next 'rngcell
Set rngCell = Nothing
Set rngList = Nothing
End Sub
'--------------------------

'To remove items from the AutoCorrect list,
'select a single column list of words to remove.
'All words in the selection must already appear in the AutoCorrect list.

Sub RemoveItemsFromAutoCorrectList()
Dim rngList As Excel.Range
Dim rngCell As Excel.Range
Set rngList = Selection
Set rngList = rngList.Columns(1).Cells
For Each rngCell In rngList
Application.AutoCorrect.DeleteReplacement rngCell.Value
Next 'rngcell
Set rngCell = Nothing
Set rngList = Nothing
End Sub
'-----------------------------


"Dantz" <[email protected]>
wrote in message
is there a way to create my own autocomplete that the list of strings
are from user inputs? please help me..as of my research I accidentally
got this code but it was really hard to modify esp. the
application.onkey...

- snip -

 

[Dantz]

thanks a lot...its good...but how can I have choices? meaning when the
user inputs "one" he will have a choice either "first" or "second" or
even more...maybe I could use a combo box then list all the choices
there..is that possible?