Only Capital letters

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Hi,
I want to change the text entered in a cell into upper case as soon as the text is entered and ENTER key is used.
Is there a way to get this either by formatting or Code.
TIA
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
I don't think you can do that with formatting.

Via code:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Target = UCase(Target)
End Sub
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Thanks A Lot.
Didn't thought that it is so simple (for those who really know.....;))
Thank You once again.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You might want to do this though, otherwise your endlessly calling the code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
    Target = UCase(Target)
Application.EnableEvents = True
End Sub
 

orysiar

New Member
Joined
Sep 14, 2006
Messages
13

ADVERTISEMENT

If you only have a few to do and you don't want to use code, you can also use the formula:

= UPPER(cell being referenced)
 

shajueasow

Well-known Member
Joined
Oct 7, 2004
Messages
1,926
Hi Hotpepper,
I had changed the code as suggested by you.
But, in my system, with Win XP and Excel 2003, there was no problems but when the code was run in Win 98 andExcel 2000 without "enableevents" the code went "mad". Why might that be so???
Not an important question to get answer, but still like to know the answer.
And, thank you orysiar, for your contribution also.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
It still is repeatedly calling the code in 2003. In my tests, there is a slight delay after changing one to enter the next one due to this code that is constantly being called.

To illustrate, this:
Private Sub Worksheet_Change(ByVal Target As Range)
Target = UCase(Target)
MsgBox "Hello"
End Sub

Change a cell and watch what happens.
You are going to have to CTRL-BREAK out of this and click on End to stop it, but as you can see you are going to get multiple msgboxes (after you hit OK, you'll get another and another, etc.) because the code is constantly running. If it actually only ran once, you would only get one box.

Use the code I posted above instead to put it back.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,726
Members
410,630
Latest member
JFORTH97
Top