Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Proper function (XP)

    I am new at Excel and VBA programming. I wanted to change the case of my text within the same cell. Ex: I want to be able to type "hello" in cell A1 and it automatically changes to "Hello". If that is remotly possible without it being a macro.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Proper function (XP)

    Only if you want to prefix your text with:
    =proper("
    and suffix it with:
    ")

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Apr 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper function (XP)

    There are a few ways of dealing with this.
    1. Use Autocorrect (Tools menu) and type hello in the left box and Hello in the right box. Beware that any time you type hello in Excel, it will automatically update to Hello.
    2. Use a check in another cell and proper it using an if statement. Eg in B1 enter =if(a1<>"",Proper(a1),""). This will put the correct format into the B column irrespective of what text is typed in the A column. Hello and hello will always be displayed as Hello. If you are printing, then you will have to hide the orginal in some way (hide the column or change the font colour to while)
    Hope this helps
    Col
    3. Use a macro which will capitalise any cell where it is run.

  4. #4
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper function (XP)

    I tried the check in another cell (B1), however that actually changed the value of B1 into Proper(A1)....
    Is there any way it can be done to change the case in A1?

    Wasn't there a Personalized formatting that could be done for this type of things in Excel 7?

    Thx

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Proper function (XP)

    I think you have run out "non-macro" solutions.

    Here is a macro solution. Add this code the worksheet object and text you add to column A will be turned into proper case.

    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Dim rCell As Range
    For Each rCell In Intersect(Target, Range("A:A"))
    If Not rCell.HasFormula Then _
    rCell = Application.WorksheetFunction _
    .Proper(rCell)
    Next
    Set rCell = Nothing
    End If
    End Sub</pre>


    Steve

  6. #6
    Lounger
    Join Date
    Apr 2004
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper function (XP)

    Thank you Steve, it works just fine.

    Sherif

Posting Permissions

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