Format Cell - Only Upper Case Alpha characters

Discussion in 'Microsoft Excel Misc' started by LinLin, Mar 20, 2009.

  1. LinLin

    LinLin Guest

    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!
     
    LinLin, Mar 20, 2009
    #1
    1. Advertisements

  2. LinLin

    CVinje Guest

    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!
    >
     
    CVinje, Mar 20, 2009
    #2
    1. Advertisements

  3. LinLin

    CVinje Guest

    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!
    > >
     
    CVinje, Mar 20, 2009
    #3
  4. LinLin

    Chip Pearson Guest

    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!
    >>
     
    Chip Pearson, Mar 20, 2009
    #4
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Jody

    Upper Case to Lower Case

    Jody, Jul 24, 2003, in forum: Microsoft Excel Misc
    Replies:
    4
    Views:
    342
    Don Guillett
    Jul 24, 2003
  2. Guest
    Replies:
    1
    Views:
    734
    Guest
    Mar 9, 2005
  3. Guest
    Replies:
    15
    Views:
    1,088
    Guest
    May 30, 2007
  4. Richard Zignego

    Changing upper case characters to upper/lower

    Richard Zignego, Dec 17, 2007, in forum: Microsoft Excel Misc
    Replies:
    1
    Views:
    420
    Gord Dibben
    Dec 17, 2007
  5. Patti
    Replies:
    2
    Views:
    3,237
    Gord Dibben
    Jan 4, 2008
  6. Sal
    Replies:
    6
    Views:
    5,280
    MrDave
    Jul 26, 2009
  7. Crankn
    Replies:
    2
    Views:
    188
    מיכ×ל (מיקי) ×בידן
    Jan 29, 2010
  8. SueW
    Replies:
    7
    Views:
    1,202
    Gord Dibben
    Feb 25, 2010
Loading...