Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Capitals

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Im sure the answer to this is staring me in the face but how do I get lowercase letters typed into a form to turn into capitals. ie if I input n, it should change to N. There is only one letter per cell. Thanks for any help. PS have had a look at the UPPER command but could not get it to work for me!!

    HELP PLEASE

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,162
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The formula for that would be (as an example, in B1)
    =UPPER(A1)
    where A1 holds the one letter value that is currently showing in lower case.

    Did you need something more than that?

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, you're going to have to use VBA for this. (I could be wrong, but this is the way I would do it)

    for this example I'm using cells A1 and B1 on as the cells that contain the text.

    Right click on the tab of the sheet that you will have the text on, and choose "View Code".

    Copy and paste this code into the editor:


    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = [A1] _
    Or Target = [B1] Then
    Target.Value = UCase(Target.Value)
    End If
    End Sub


    You can change this code by changing A1 and B1 to be whatever cells you want to capitalise.

    I hope this makes sense.

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If the lowercase letter is entered in say A1, the uppercase letter also has to be in A1. The function you quoted takes the lowercase from one cell and puts the Capital in another cell.
    I thought there might be a cell format option that I couldn't find that would be possible to use. There is something similar in Word that can be used when working with tables.

    Any other idea's?

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-15 21:10, Hellhole wrote:
    If the lowercase letter is entered in say A1, the uppercase letter also has to be in A1. The function you quoted takes the lowercase from one cell and puts the Capital in another cell.
    I thought there might be a cell format option that I couldn't find that would be possible to use. There is something similar in Word that can be used when working with tables.

    Any other idea's?
    Hi hellhole
    The procedure Mark gave you is A
    Event procedure. It LOOKS @ A1 & B1....so that if you type in any letter it converts it to Uppercase.
    If you use this then you will have to change
    the range you need to monitor to get uppercases into the cells.


    Post if still not clear

    Ivan

  6. #6
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just another, similar one...

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    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

    To get all entries in column A through G to turn uppercase regardless of entry, change the range to "A:G"
    ~Anne Troy

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-15 22:18, Dreamboat wrote:
    Just another, similar one...

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    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

    To get all entries in column A through G to turn uppercase regardless of entry, change the range to "A:G"
    Great stuff. Thanks for your help everyone.
    Not as somple as I thought it would be (ie a format option) but this works just great. So thanks once again.

    Hellhole - London UK

  8. #8
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well, okay, I admit. I actually stole it from:

    http://www.cpearson.com/excel/case.htm

    I like to give credit where credit is due.
    ~Anne Troy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •