Disable AutoComplete with VB Code for a Single Workbook

[Dickie Worton]

Hi!
Can anyone suggest how I can use VB code to disable the AutoComplete
functionality in a spreadsheet I am developing, please?
I need to ensure that the entries in each cell are as typed in by the user
and have not 'autocompleted' to that of a previous entry the same column. As
part of what we are trying to do is identify possible duplicate records I
need to be confident that any that we do identify are legitimate and have not
come about because of AutoComplete.
I know how to disable this via Tools:Options but once the user exists the
workbook and moves on to another piece of work this setting will remain
disabled unless they go back via Tools:Options and enable it once more.
Without being unkind I think that this may be beyond some of our users, hence
the need for a spreadsheet that is as 'foolproof' as possible (i.e. no
AutoComplete).
I did find some previous postings on this subject but the code didn't appear
to work, does anyone out there have a fix for this problem?
Thanks,
Dickie

 

[Mike H]

Hi,

Try:-

Application.EnableAutoComplete = False
do your stuff
Application.EnableAutoComplete = True


Mike

 

[Mike H]

Dickie,

Perhaps this would be better;-

Private Sub Workbook_Open()
Application.EnableAutoComplete = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableAutoComplete = True
End Sub

That way it would be disabled the entire time a particular workbook was open
and re-enabled on closing.

Mike

 

[Dickie Worton]

Mike,
Thanks for the prompt reply.
I took the code you suggested and pasted this into my workbook, then saved
and exited before re-opening. However when I entered text into a cell it used
AutoComplete to 'fill-in' the same name as appeared in the cell directly
above it, so either it doesn't work or I've done something wrong.
Any more suggestions gratefully accepted...
Regards,
Richard

 

[Dickie Worton]

Mike,
Thanks for your second posting, I think this made it on before my reply!
Sadly the code included within didn't work for me either.
Regards,
Dickie

 

[Mike H]

Dickie,

Correctly applied the code will disable autocomplete.

Alt+F11 to open VB editor
Double click This workbook
On the left dropdown in the right panel select "Workbook"
On the right dropdown in the right panel select "Workbook_Open"
Paste in Application.EnableAutoComplete = False
On the right dropdown in the right panel select "Workbook_Before_Close"
Paste in Application.EnableAutoComplete = True

Save and exit the workbook and re-open

Mike

 

[Dickie Worton]

Hi Mike,
Don't know what I did wrong previously but have followed your excellent
instructions below and it now works just as I want it.
Many, many thanks,
Dickie