Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using PROPER (2000)

    In a spreadsheet where various users enter data, I get variations of capitalization...some all lower case, some upper case, etc. The PROPER function works great if referencing a different cell, although I do not seem to be able to work around using it in the cell where it is needed. Is there a way to get around the circular reference and somehow do in A1, =Proper(A1)?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    Mike,

    It should be much easier to use ASAP-Utilities for this. It's a free download at:

    http://asap-utilities.com

    Aladin
    Microsoft MVP - Excel

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    No, there is no way to do this.

    There are a couple of things that you can do:

    1- Use the Proper function in an empty column. Copy that column and then do a Paste/Special/Values back over the original column. Then delete the column with the Proper function.

    2- Use a VBA routine like the one below which will perform the Proper function on all the cells in the current selection:

    <pre>Public Sub ChangeToProper()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = Application.WorksheetFunction.Proper(oCell.Value)
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    Hi Legare,

    May I also suggest a Worksheet_Change event covering the area where the data is entered. As you would be well aware, the advantage of this approach is that the whole changing to Proper Case happens automatically.

    To be inserted into the code for the relevant sheet(s)

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'Inserts Proper Case in the nominated area

    On Error GoTo EndMacro

    If Application.Intersect(Target, Range("C5:F33")) Is Nothing Then
    Exit Sub
    End If

    If Target.Cells.Count > 1 Then
    Exit Sub
    End If

    If Target.Value = "" Then
    Exit Sub
    End If

    Application.EnableEvents = False

    With Target
    If .HasFormula = False Then
    .Value = Application.WorksheetFunction.Proper(.Value)
    End If

    End With
    Application.EnableEvents = True
    Exit Sub
    EndMacro:
    Application.EnableEvents = True
    End Sub

    Good Luck!

    Peter Moran

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    Thanks Legare and Peter, I had surmised that this would be the ultimate approach. Might try the asap utilities also Aladin.

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    Actually, what I finally did was to build the PROPER function into the rollup spreadsheet. All of the information submitted is rolled up into a master sheet, and prepending the links with PROPER ensured that all of the information looks the same. Thanks.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using PROPER (2000)

    Good idea. I would have used the following somewhat simpler and more efficient code:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Intersect(Target, ActiveSheet.Range("A:A")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    For Each oCell In Intersect(Target, ActiveSheet.Range("A:A"))
    oCell.Value = Application.WorksheetFunction.Proper(oCell.Value)
    Next oCell
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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