AUTO CORRECT PROBLEM

[Chris]

I am working in Excel 03. I am constructing a large database whereby in
certain cells when an individual enters A, E or P it is auto corrected to a
numeric value. To my horror these auto corrections have carried on in Word,
Outlook and PowerPoint. How do I restrict these autocorrects to just the
spreadsheet I am working on?

 

[Bob I]

As you have observed the AutoCorrect is a Suite wide file. At the least
you would do a find and replace or use a macro to do this.

 

[Gord Dibben]

Chris

You can use a helper cell to return a number based upon a lookup table with
letters and numbers.

Or you could use event code to change the letters to numbers as you enter them.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A20") 'adjust range to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("A", "E", "P") 'add more if needed
nums = Array(8, 9, 6) 'add more if needed
For Each rr In r
ivalue = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ivalue = nums(i)
End If
Next
If ivalue > 0 Then
rr.Value = ivalue
End If
Next
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that module. Edit to suit then Alt + q to return to
the Excel window.


Gord Dibben MS Excel MVP