Change Case

Jayden · Mar 12, 2004

I am just wondering if there is a way to change the font case in excel.

This is want I have. I have created a template for which sorts info supplied form a email. What I do is copy the entire email and past it into a spacific cell in a spreadsheet. Then the formulas that I have set up put the email into a better format. The problem is that not all the e-mails are the same case some have upper case and some are all lower. Is there a way that I can change them automaticly with excel instead of retyping them?

thanks

Smitty · Mar 12, 2004

Take a look at =PROPER.

Or this nifty converter that Ivan wrote:

Code:

Sub TextCon()
'code by Ivan F. Moala
    Dim ocell As Range, ans As String
    
    ans = Application.InputBox("Type in Letter" & vbCr & _
        "(L)owercase, (U)ppercase, (S)entence, (T)itles ")
    If ans = "" Then Exit Sub
    
    For Each ocell In Selection.SpecialCells(xlCellTypeConstants, 2)
        Select Case UCase(ans)
            Case "L": ocell = LCase(ocell.Text)
            Case "U": ocell = UCase(ocell.Text)
            Case "S": ocell = UCase(Left(ocell.Text, 1)) & _
                LCase(Right(ocell.Text, Len(ocell.Text) - 1))
            Case "T": ocell = Application.WorksheetFunction.Proper(ocell.Text)
        End Select
    Next

End Sub

Hope that helps,

Smitty

Jayden · Mar 12, 2004

I inserted the code but I am not sure if I am doing it right. I placed the code and ran the Macro but the case stayed the same.

I am Getting a 1004 Run time error. any reason

Brian from Maui · Mar 12, 2004

In addition to Smitty's use of Porper and Ivan's code, have a look at ASAP Utilities.....

www.asap-utilities.com

Smitty · Mar 12, 2004

I am Getting a 1004 Run time error.

What line is it bombing on?

I also should have pointed out that in addition to PROPER, there are UPPER & LOWER functions as well.

Hope that helps,

Smitty

Jayden · Mar 12, 2004

It's dropping out at the "Next" statement.

Smitty · Mar 12, 2004

Is your worksheet protected?

What's the error message?

Greg Truby · Mar 12, 2004

I'm not sure what's the source of your problem's with Ivan's code. It works reasonably well for me (a few glitches with Sentence case, but that may be happening because of merged cells).

Anyhow, I wrote most of this a while ago, but went back and tried to shore up some weaknesses. It does not ask which case you want, but rather it toggles through U-->L-->P. It does this on a cell-by-cell basis. It will also work for some drawing objects and chart objects.

Hope you find it useful:

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sub ChangeCase()
'_______________

' Toggles case on items in selection. Selection can be
' cells, certain drawing objects and certain chart objects.

' written by Greg Truby

 If TypeName(Selection) <> "Range" Then GoTo OtherItemSelected

 Dim strMsg As String
 Dim r As Range, r1 As Range, r2 As Range

 On Error Resume Next
 Set r1 = Selection.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlTextValues)
 If r1 Is Nothing Then
 If ActiveSheet.ProtectContents Then
 strMsg = "This macro will fail if the worksheet is protected."
 Else
 strMsg = "No text cells in selection. Only numbers, dates &/or formulas"
 End If
 MsgBox strMsg, vbExclamation, "No Text Cells"
 Exit Sub
 End If

 Set r = Selection
' UndoLoad r
 For Each r1 In r.Cells
 Set r2 = r1.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlTextValues)
 If Not r2 Is Nothing Then
 If Not Intersect(r1, r2) Is Nothing Then
 If r2.Count > r1.Count Then Set r2 = r1
 r2.Value = ChangeTextCase(r2.Value)
 End If
 End If
 Next r1
' Application.OnUndo "Undo ChangeCase", "UndoRestore"
 Exit Sub

OtherItemSelected:
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
 On Error GoTo ErrorHandler
 Dim varObject As Variant
 If TypeName(Selection) = "DrawingObjects" Then
 For Each varObject In Selection
 CaseForShapes varObject
 Next varObject
 Else
 CaseForShapes Selection
 End If

 Exit Sub

ErrorHandler:
'¯¯¯¯¯¯¯¯¯¯¯¯
 Beep
 MsgBox "Could not execute macro." & vbCrLf & vbCrLf & _
 "Check to see if the Worksheet or Workbook has Protection turned on.", _
 vbCritical, "Error"
End Sub

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Sub CaseForShapes(varObject As Variant)
'______________________________________

 Dim strText As String
 Select Case TypeName(varObject)
 Case "Rectangle", "Oval"
 strText = varObject.Characters.Text
 strText = ChangeTextCase(strText)
 varObject.Characters.Text = strText
 Case "TextBox", "ChartTitle", "AxisTitle"
 strText = varObject.Text
 strText = ChangeTextCase(strText)
 varObject.Text = strText
 Case Else
 Beep
 End Select
End Sub

'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
Function ChangeTextCase(ByVal strText As String) As String
'_________________________________________________________

 If strText = UCase(strText) Then
 strText = LCase(strText)
 ElseIf strText = LCase(strText) Then
 strText = StrConv(strText, vbProperCase)
 Else
 strText = UCase(strText)
 End If
 ChangeTextCase = strText
End Function

Oh yeah, I commented out a couple of lines that refer to making the case change Undo-able.

Regards,

Legacy 21301 · Mar 12, 2004

Legacy 21301 · Mar 12, 2004

To Greg Truby

With your code as it stands, if only one cell is selected that is not a text cell, r1 will be set as all of the text cells on the worksheet by the line that reads :-

Code:

Set r1 = Selection.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlTextValues)

Suggest that it should be replaced with something like this :-

Code:

If Selection.Cells.Count = 1 Then
    Set r1 = Selection
    If Selection.HasFormula Or TypeName(Selection.Value) <> "String" And IsNumeric(Selection) Then Set r1 = Nothing
Else
    On Error Resume Next
    Set r1 = Selection.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlTextValues)
End If

The same comment applies to Ivan Moala's procedure.
With that code, if only one cell (of any type) is selected, ALL of the cells on the worksheet that contain text constants will be converted.

Change Case

Jayden

Board Regular

Excel Facts

Smitty

Legend

Jayden

Board Regular

Brian from Maui

MrExcel MVP

Smitty

Legend

Jayden

Board Regular

Smitty

Legend

Greg Truby

MrExcel MVP

Legacy 21301

Guest

Legacy 21301

Guest

Similar threads

Forum statistics

Share this page

Change Case

Board Regular

Excel Facts

Legend

Board Regular

MrExcel MVP

Legend

Board Regular

Legend

MrExcel MVP

Legacy 21301

Guest

Legacy 21301

Guest

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock