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?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.

Share This Page