Rick;

When you reach the mid 70's, memory sometimes really sucks. After reading
your reply a number of times I had a wakeup at 3:00 am this morning thinking
that the code was to make my life a little easier. That column I believe
used to be for Zip/Postal codes. Without changing case, I would enter a
zipcode 983119667 and it would automatically convert it to 98311-9667 or a
postal code of k1e2l2 to K1E 2L2. Does this seem logical?

--

Regards
Michael Koerner


"Rick Rothstein" wrote in message
...
That looks like code I wrote... I don't remember it, but the coding style
appears to be mine. Anyway, what this code is doing is looking down Column M
and if a cell in that column has either a 6 or 9 character value in it (a
constant, not a formula result), then it makes the characters upper case and
inserts either a space or a dash in the "middle" depending on whether the
length was 6 or 9 characters. So, an entry like "abcdef" would become "ABC
DEF" and an entry like "abcdefghi" would become "ABCDE-FGHI". It appears
that this functionality is deliberately wanted, so removing the UCase
functionality **may** not be what you want to do. On the other hand, you may
have simply done a Clear All on the sheet and reused it for some other
purpose, forgetting that the event code was there. You have to decide which
is the case. Anyway, if the code's functionality is actually needed, then
just removing the UCase function calls would be the wrong way to go. If, on
the other hand, this sheet is being reused for a different purpose, the the
solution would be to delete the Worksheet_Change procedure in its entirety.

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message
...
Absolutely correct, thanks. Here is the code I found. How did it get
there?
and how do I get rid of it?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Temp As String
If Intersect(Range(Target(1).Address), _
Range("M:M")) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
With Target
If Not .HasFormula Then
Temp = Replace(UCase(Target.Value), " ", "")
If Len(Temp) = 6 Then
.Value = Format(Temp, "@@@ @@@")
ElseIf Len(Temp) = 9 Then
.Value = Format(Temp, "@@@@@-@@@@")
End If
End If
End With
With Target.Offset(0, -1)
.Formula = UCase(.Formula)
End With
ErrHandler:
Application.EnableEvents = True
End Sub

--

Regards
Michael Koerner


"Rick Rothstein" wrote in message
...
Do you, perhaps, have event code running for that worksheet? If so, maybe
it
is performing the upper casing of text. You can see if there is any event
code for that worksheet by right clicking its tab (at the bottom of the
sheet) and selecting View Code from the popup menu that appears. This will
take you to the worksheet's code window... if you see any code on the
sheet,
then you (probably) have event code running. I would look for one that has
this header line...

Private Sub Worksheet_Change(ByVal Target As Range)

Between that line and the first End Sub line below it will probably be a
line that has the UCase function (or maybe the worksheet's UPPER function)
as part of an assignment operation... if you find that, then that is what
is
doing the upper casing of your text. If you copy/paste all the code in the
procedure (between the Worksheet_Change line and its Exit Sub line), then
someone here will see if they can recommend how you should proceed.

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message
...
For some unknown reason when ever I enter Canada in cell L366 and press
enter it automatically changes to CANADA. When I enter a-c-d-g-h in cell
M366 it automatically changes to A-C-D--G-H. I have tried replacing the
whole row, but get the same results

--

Regards
Michael Koerner