Text Changes Case Automatically

  • Thread starter Thread starter Michael Koerner
  • Start date Start date
M

Michael Koerner

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
 
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.
 
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


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 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.
 
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
 
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
 
Thanks Rick, looks like deleting is he way to go.

--

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;

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


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.
 
Back
Top