Format Cell - Only Upper Case Alpha characters

L

LinLin

Hi Everyone

Is there anyway to format a cell so that if data is entered as a lower case
alpha character, it will automatically change it to Upper case?

IE: I enter a

And excel comes back with A

Also for a combination of letters:

help
comes back as: HELP

I am not so concerned with a mix of lower and upper case (of course, if the
solution can ensure a mix becomes all upper case, that would be cool too!)

thanks!
 
C

CVinje

Here's an answer I found that worked when I tested it - taken from:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=70

Hope it works for you!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'Change A1:A10 to the range you desire
'Change UCase to LCase to provide for lowercase instead of uppercase

If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub


How to use:

Copy the code above.
Open a workbook.
Right-click the worksheet on which you'd like this code to operate, and hit
View Code.
Paste the code into the code window that appears at right.
Change the range A1:A10 in the code to the range suitable for your file.
Save the file, and close the Visual Basic Editor window.


Test the code:

Type any text into the range of cells you chose in your code.

CVinje
 
C

Chip Pearson

I would be careful with that. Add some code to ensure that you are not
overwriting a formula:
Target(1).Value = UCase(Target(1).Value)

should be

If Target(1).HasFormula = False Then
Target(1).Value = UCase(Target(1).Value)
End If

Just to be complete, you might also want to ensure you're not working
in an array:

If Target(1).HasFormula = False Then
If Target(1).HasArray = False Then
Target(1).Value = UCase(Target(1).Value)
End If
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top