Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Cell - Only Upper Case Alpha characters
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Cell - Only Upper Case Alpha characters
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 "LinLin" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Cell - Only Upper Case Alpha characters
Something that I didn't mention - when you use a code and it takes an action
on the sheet / workbook (i.e. - make everything capitalized), you loose the ability to Undo. Just FYI. "CVinje" wrote: 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 "LinLin" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Format Cell - Only Upper Case Alpha characters
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) On Thu, 19 Mar 2009 22:50:01 -0700, CVinje wrote: 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 "LinLin" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format cell to convert to Upper case | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
how do I format a cell to display its contents in Upper case? | Excel Discussion (Misc queries) |