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

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

If you open the workbook with macros disabled, does the problem go away?

Michael Koerner wrote:

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

--

Dave Peterson

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

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

How did it get there?

Someone pasted it into that sheet module.

Select the text and hit delete.

Save the workbook.


Gord Dibben MS Excel MVP

On Fri, 16 Oct 2009 15:00:48 -0400, "Michael Koerner"
wrote:

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.

That works for me, thanks very much

--

Regards
Michael Koerner


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
How did it get there?

Someone pasted it into that sheet module.

Select the text and hit delete.

Save the workbook.


Gord Dibben MS Excel MVP

On Fri, 16 Oct 2009 15:00:48 -0400, "Michael Koerner"
wrote:

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.

Thanks Rick, looks like deleting is he way to go.

--

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

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

I knew that code look familiar... it **was** mine... and I posted it in
response to your inquiry...

http://groups.google.com/group/micro...3216eb864cc835

The above link should take you to the middle of a thread you started where,
in a follow up question, you asked for that functionality.

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message
...
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

Definitely going to sleep better tonight <g thanks very much

--

Regards
Michael Koerner


"Rick Rothstein" wrote in message
...
I knew that code look familiar... it **was** mine... and I posted it in
response to your inquiry...

http://groups.google.com/group/micro...3216eb864cc835

The above link should take you to the middle of a thread you started where,
in a follow up question, you asked for that functionality.

--
Rick (MVP - Excel)


"Michael Koerner" wrote in message
...
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