Macro for autocomplete

Hi,

1) The list of names on the sheet2.
2) When enter first letters in Column A macro has to show autocomplete in cell on the sheet1.

You may find example as attachment.

Here is a link that describes what you want without using VBA. http://www.ozgrid.com/Excel/autocomplete-validation.htm

Now .... I have Excel 2007 .. so I can't help you find the setting in Excel 2016 but it must be there somewhere.

In 2007 it is located : Excel Options (Upper Left) / Advanced / Editing Options (select check box "Enable AutoComplete for cell values"


Thanks for asking this question, it is a 'tool' I wasn't aware of with the information included in the URL link above.

Thanx for reply.

I don't have possibility to paste values above on the sheet1.
It should be some macro decision.

Originally Posted by shg

There's an add-in at https://app.box.com/shared/ono8ni9xvr that will do that, a ReadMe at https://app.box.com/shared/4qxybepxxr, and an example workbook at https://app.box.com/shared/e8dbbple34

Thank you. It's working.
But I really need macro for this task.

Or you could make it change automatically to the full name when you enter the short codes by adding them to the auto correct dictionary when opening the file and removing them on closing the file

I think the below should do it, if you create a list of the things to auto correct in a sheet called autoC in column A an the things to correct to in column b. (starting from row 2 to allow for a header row)

Private Sub Workbook_Open()
For i = 2 To 1000
If IsEmpty(Sheets("autoC").Cells(i, "A")) Then
Exit Sub
End If
Application.AutoCorrect.AddReplacement What:=Sheets("autoC").Cells(i, "A"), Replacement:=Sheets("autoC").Cells(i, "B")
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For i = 2 To 1000
On Error Resume Next
If IsEmpty(Sheets("autoC").Cells(i, "A")) Then
Exit Sub
End If
Application.AutoCorrect.DeleteReplacement Sheets("autoC").Cells(i, "A")
Next
End Sub

Originally Posted by scottiex

Or you could make it change automatically to the full name when you enter the short codes by adding them to the auto correct dictionary when opening the file and removing them on closing the file

I think the below should do it, if you create a list of the things to auto correct in a sheet called autoC in column A an the things to correct to in column b. (starting from row 2 to allow for a header row)

Private Sub Workbook_Open()
For i = 2 To 1000
If IsEmpty(Sheets("autoC").Cells(i, "A")) Then
Exit Sub
End If
Application.AutoCorrect.AddReplacement What:=Sheets("autoC").Cells(i, "A"), Replacement:=Sheets("autoC").Cells(i, "B")
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
For i = 2 To 1000
On Error Resume Next
If IsEmpty(Sheets("autoC").Cells(i, "A")) Then
Exit Sub
End If
Application.AutoCorrect.DeleteReplacement Sheets("autoC").Cells(i, "A")
Next
End Sub

I tried to do this but no actions in file.
Would you please check the attachment?
https://app.box.com/s/ngmdwt6fqo9q7y44nv0mb2qh30v3ufl3