Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change Case (Excel 2003)

    Welcome back all,

    I have a small little code (see below) that allows me to change the case of the select cells within an excel spreadsheet. However i am unable to get it to allow me to only select one cell. If I have only one cell selected then it changes the whole sheet. Hopefully someone here will be able to assist me with tweaking this little code to allow me to either select multiple cells or just one.

    Sub UpperCase()
    Dim Rng As Range
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
    xlTextValues).Cells
    If Err.Number = 0 Then
    Rng.Value = StrConv(Rng.Text, vbUpperCase)
    ' Rng.Value = StrConv(Rng.Text, vbLowerCase)
    ' Rng.Value = StrConv(Rng.Text, vbProperCase)
    End If
    Next Rng
    End Sub

    Thank you in advance.
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Case (Excel 2003)

    You could use something like this:

    Sub UpperCase()
    Dim Rng As Range
    If Selection.Count = 1 Then
    If Not Selection.HasFormula Then
    Selection = UCase(Selection)
    End If
    Else
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
    Rng = UCase(Rng)
    Next Rng
    End If
    End Sub

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Case (Excel 2003)

    Thank you HANS that worked a treat.

    I have one more question for you. In my original post I had 2 other lines commented out, one for lower case and one for proper case (sentence case) from your suggestion i can get the lower case one but i can't seem to find an option to get the proper case working. I have tried PCase and SCase and ProperCase all of which come back saying that they are not recognized.

    Any Suggestions.

    Thank you for all your help.
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Case (Excel 2003)

    For proper case, you do need the StrConv function with vbProperCase as the second argument; there is no PCase or similar function.

    Sub ProperCase()
    Dim Rng As Range
    If Selection.Count = 1 Then
    If Not Selection.HasFormula Then
    Selection = StrConv(Selection, vbProperCase)
    End If
    Else
    For Each Rng In Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Cells
    Rng = StrConv(Rng, vbProperCase)
    Next Rng
    End If
    End Sub

  5. #5
    New Lounger
    Join Date
    Nov 2006
    Location
    Adelaide, South Australia, Australia
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Case (Excel 2003)

    Thank you Hans,

    You have worked your magic yet again.
    Mad Penguin
    Everything is Possible, the Impossible just takes Longer
    <img src=/S/coffeetime.gif border=0 alt=coffeetime width=32 height=48>

Posting Permissions

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