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
"Mike H" wrote:
> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > >
|
Similar Threads
