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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,141,011
Messages
5,703,728
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top