Auto Complete Off & on, Excel 2000

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Is it possible to turn autocomplete off for just 1
workbook? When I try to do this, it turns is off in all
my workbooks, which is not acceptable.
I am using the formula =LEN to count characters in a cell
(like using "hash marks" when counting on pen & paper).
Obviously the auto complete destroys the count in each
cell. Using different characters for each column is not
an option as this is for users with little Excel
background.
Many thanks
Tom
 
Tom,

you can use a macro that turns this off when you open this particular
workbook
and another when you close the workbook that turns it on.
In this case record 2 macros, one macro where you turn it off and the other
when you turn
it on again. Then press Alt + F11, find the workbook in the project pane to
the left,
copy this part

Application.EnableAutoComplete = False

double click ThisWorkbook in the same pane and from the dropdown select
Workbook and paste the line there,
will look like

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

Repeat but also select from dropdown to the right and select BeforeClose
paste in the second macro line

will look like

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


Delete the recorded macros by selecting them and right click and select
remove module, when prompted to export select no

Press Alt + Q to close the editor and save the workbook. Make sure you have
medium security
so you can choose to enable macros when you open workbooks and obviously in
this case you should enable
them. Which means that now when this wb is opened you turn it off and when
you quit it is back on..
 
Back
Top