Page 1 of 2 12 LastLast
Results 1 to 10 of 13

First Letter Each Word UpperCase- Both for Cells & TextB

This is a discussion on First Letter Each Word UpperCase- Both for Cells & TextB within the Excel Questions forums, part of the Question Forums category; Hi, How to format so that the first letter of each word entered into a cell (and Also for a ...

  1. #1
    Board Regular I_AM's Avatar
    Join Date
    Jul 2002
    Location
    New Zealand
    Posts
    139

    Default First Letter Each Word UpperCase- Both for Cells & TextB

    Hi,
    How to format so that the first letter of each word entered into a cell (and Also for a TextBox) is UpperCase and the rest of the word is LowerCase?

    Thanks Regards Ron

  2. #2
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    hi!
    have you tried the proper function?

    like this.


    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    meMe**
    2
    myMy**
    3
    my*nameMy*Name**
    4
    hello*worldHello*World**
    Sheet1*

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    There is always a better way!!

  3. #3
    Board Regular I_AM's Avatar
    Join Date
    Jul 2002
    Location
    New Zealand
    Posts
    139

    Default Re: First Letter Each Word UpperCase- Both for Cells & T

    Hi
    Thanks for that. In your example I wish to type "me" in Cell("A1") and have then Cell(A1") display "Me" with out referencing other cells. Is this possable? Also hopeing for similer with a TextBox via VBA.

    Thanks regards Ron

  4. #4
    Board Regular I_AM's Avatar
    Join Date
    Jul 2002
    Location
    New Zealand
    Posts
    139

    Default Re: First Letter Each Word UpperCase- Both for Cells & T

    Hi
    Thanks for that. In your example I wish to type "me" in Cell("A1") and have then Cell(A1") display "Me" with out referencing other cells. Is this possable? Also hopeing for similer with a TextBox via VBA.

    Thanks regards Ron

  5. #5
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    hi!
    if you wan to type in "me" in A1 and have it become "Me", then i dont know of any formula. but in VBA yes.
    try this.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ****Target.Value = Application.WorksheetFunction.Proper(Target.Value)
    End Sub
    There is always a better way!!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: First Letter Each Word UpperCase- Both for Cells & T

    Hi I_Am:

    You may want to try ...
    Code:
    activecell=StrConv(activecell,vbProperCase)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  7. #7
    Board Regular I_AM's Avatar
    Join Date
    Jul 2002
    Location
    New Zealand
    Posts
    139

    Default Re: First Letter Each Word UpperCase- Both for Cells & T

    Thanks once again,
    That certainly works, however it stops some of my other code from working giving an "error 13 Type mismatch". Also if i try to highlight an entire Row I get the same error?
    Regards Ron

  8. #8
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    ok!
    I understand that it will give you that erro if you highlight multiple cells. this will solve that.
    Private Sub Worksheet_Change(ByVal Target As Range)
    for each c in target
    c.Value = Application.WorksheetFunction.Proper
    (c.Value)
    next c
    End Sub

    but in regards to your other code, you may try to make calcualtions that are not compatible such as multiplying text and a number, etc.
    show your code that gives error.
    There is always a better way!!

  9. #9
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    hi!
    i think i miss something.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ****Application.EnableEvents = False
    ****For Each c In Target
    ********c.Value = Application.WorksheetFunction.Proper(c.Value)
    ****Next c
    ****Application.EnableEvents = True
    End Sub
    There is always a better way!!

  10. #10
    Board Regular I_AM's Avatar
    Join Date
    Jul 2002
    Location
    New Zealand
    Posts
    139

    Default Re: First Letter Each Word UpperCase- Both for Cells & T

    Thank you both,
    Much appreciated! Yogi gets the prize on this one as it slots in to my code easily. Thanks again Regards ron

Page 1 of 2 12 LastLast

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
  •  


DMCA.com