How do I stop excel from helping with autofill?

[OaklandJim]

Team Mr. Excel

In a first name cell or a last name cell user inputs some or all letters of a name. Event handler then goes to data and looks up all qualifying records from a database. Example: type in da into last name cell and names Danials, Danson and David are found.

It all works well. BUT, recently I have been getting an annoying issue that I have no idea how to fix.

Apparently at one point I must have typed the words First Name and Last Name into the respective cells because now if I enter say the letter f (lower case) into the first name cell the words First Name are automatically entered by Excel into the cell (AutoComplete). Same for last name cell: if I enter an l (lower case L) Excel enters the words Last Name.

So how do I stop excel from helping with autofill?

If I turn it off in File > Options > Advanced > "Enable AutoComplete" it is off for all workbooks. And this is for a client so disabling autocomplete will not work when I send the file to her.

I appreciate your response in advance.

Jim

 

[OaklandJim]

I'd still like to know how to turn off autocomplete with vba but I found a kluge. The reason that I was getting that issue is because there were two headers in my worksheet reading "First Name" in the same column as first name input cell. And there are two headers with the text "Last Name" in the same column as the last name input cell. So Excel was helping by autofilling First Name or Last Name: text found in the same column as input cells.

As unsatisfying as it is; the klugey fix was to add a space in front of the headers' text. That way Excel did not see the offending text in the respective column.

There must be a better way?

I tried using

Application.AutoCorrect.AutoFillFormulasInLists = False

Thanks again.

Jim