Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    California
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Font =PROPER (2002)

    I'm trying to change my font to have an initial cap setting like what's available in Word. All I've been able to come up with is =PROPER, but it doesn't want to allow me to apply it to the cell I'm working on, only if I want to reference a cell elsewhere? I'm NOT Excel savvy...

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font =PROPER (2002)

    This macro does what you want, select the text you want to convert then run the macro.

    Sub Proper()

    For Each cel In Selection
    gg = cel.Value
    cel.Value = "=Proper(""" & gg & """)"
    Next cel
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False


    End Sub

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    California
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font =PROPER (2002)

    Worked like a charm! THANK YOU MICHAEL
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  4. #4
    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: Font =PROPER (2002)

    This will be much faster, it doesn't use copy paste to get the values and only loops thru constants with text, it will ignore all else in the selection.

    Steve

    <pre>Sub SetProper()
    Dim rCell As Range
    For Each rCell In Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
    rCell.Value = _
    Application.WorksheetFunction.proper(rCell)
    Next
    End Sub</pre>


  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font =PROPER (2002)

    Yes that's good. I've never had a problem with the speed of my macro, although I don't use it very often. I do remember it was a bit of a pain to write as Proper isn't (or wasn't at the time) a VBA function. Your way is much neater.

  6. #6
    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: Font =PROPER (2002)

    Many (not all) of the functions that are available in excel (but have no VB equivalent) can be accessed via:
    <pre>Application.WorksheetFunction. </pre>

    followed by the function name.

    If you go to the immediate window and enter (eg) proper, highlight it and hit <F1> you will get a list of all the "worksheet functions" available in VB. [This works in XL97, and I assume it will work in other versions]

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font =PROPER (2002)

    Excellent.

    Thank you.

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Font =PROPER (2002)

    In Excel 2003 (version 11.5612.5606), you have to type "proper" into Search Results on the Task Bar. For reasons best known to Microsoft, Steve's recommendation brings up an Internet Explorer "Page Not Found" error.
    Gre

Posting Permissions

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