Disable AutoComplete with VB Code for a Single Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Hi,

Try:-

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


Mike
 
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
 
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
 
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
 
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
 
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
 
Back
Top