Converting Cells to Proper Case

The following macros are much simpler than the ones of this tip and do the very same job:

Sub MakeProper()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then cell = Application.proper(cell)
Next
End Sub

Sub MakeLower()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then cell = LCase(cell)
Next
End Sub

@ srihari
Oops, the macro I proposed overwrites the formula with it's value. Here is still a very one who preserves formulas:

Sub proper()
Dim cell As Range
For Each cell In Range("A1:B2")
If Not cell.HasFormula Then cell = Application.proper(cell)
Next
End Sub

@srihari
If you had clicked on the Help button in the dialog box when the error occured you would have seen that declaring a variable twice in the same prodecure is not allowed. So removing the second
   Dim rngSrc As Range
   Dim lMax As Long, lCtr As Long
will solve it.

But you don't need such an elaborated macro. You can reduce the code to one line with the very same result:

Sub proper()
Range("A1:B2") = Application.proper(Range("A1:B2"))
End Sub

Sub proper()

Range("A1:A2").Select

Dim rngSrc As Range
Dim lMax As Long, lCtr As Long

Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
lMax = rngSrc.Cells.Count

For lCtr = 1 To lMax
If Not rngSrc.Cells(lCtr).HasFormula Then
rngSrc.Cells(lCtr) = Application.proper(rngSrc.Cells(lCtr))
End If
Next lCtr



Range("B1:B2").Select

Dim rngSrc As Range
Dim lMax As Long, lCtr As Long

Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
lMax = rngSrc.Cells.Count

For lCtr = 1 To lMax
If Not rngSrc.Cells(lCtr).HasFormula Then
rngSrc.Cells(lCtr) = Application.proper(rngSrc.Cells(lCtr))
End If
Next lCtr


End Sub

compile error duplicate declaration in current scope

Rick, your Sub ProperUpperLowerToggle() is elegant. Is there a way to add the sentence case to this code?

@Graham,

What an intriguing idea to make the subroutine itself Static as opposed to making the included variables Static instead. I have been programming in various forms of BASIC (the predecessor to VB) and VB/VBA since 1981 and it never dawned on me in all that time to make the procedure itself Static rather than its included variables. I will have to think about all the ramification about doing it that way, but thank you for posting it in the first place. As for your code that toggles between the three formatting options, here is my code modified to do that using a plain old Static variable for now ;-)

Sub ProperUpperLowerToggle()
Static X As Long
X = 1 + (X Mod 3)
Selection = Evaluate(Replace("IF(@="""","""",CHOOSE(" & X & ",PROPER(@),UPPER(@),LOWER(@)))", "@", Selection.Address))
End Sub

Another method that toggles through all 3 options - lower, proper and upper case.
Includes a step to stop the macro if the user selects an area beyond the currently used cells - for example the entire worksheet.
I have used this in Excel 2000, 2003 and 2010.
Also I have attached the macro to a button on the toolbar/ribbon for easier usage.

====================
Option Explicit

Static Sub CaseChange()

' Moves between Lower, Proper and Upper Case for selected fields.

On Error GoTo ErrorTrap

Dim item, shiftcase, inputFormula, cellnumbers, message

Application.ScreenUpdating = False 'Runs faster & stops screen flicker
Application.Calculation = xlManual
ActiveWorkbook.PrecisionAsDisplayed = False

shiftcase = shiftcase + 1
' initial value of shiftcase is zero if "first use" or
' "last used" value, due to "Static" statement in Sub title
If shiftcase > 3 Then shiftcase = 1
For Each item In Selection
If item.Row > Cells.SpecialCells(xlLastCell).Row Or item.Column > Cells.SpecialCells(xlLastCell).Column Then
'Prevent possible changes to large number of unused cells, e.g. down to row 65,536 !!
cellnumbers = "R" & Cells.SpecialCells(xlLastCell).Row & "C" & Cells.SpecialCells(xlLastCell).Column
inputFormula = cellnumbers
message = "Selection included cells beyond the last USED column and/or row, process stopped !!" & String(2, 10) & String(2, 13) & "The correct results may have been achieved, although these may not appear until AFTER you click 'OK'" & _
String(2, 10) & String(2, 13) & "However you may need to repeat the task, avoiding selection of any cells either :-" & String(2, 10) & String(2, 13) & _
"BELOW or TO THE RIGHT of the last USED cell. The last USED cell is "
message = message & _
Application.ConvertFormula(Formula:=inputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1, toAbsolute:=xlRelative) & "."
MsgBox message
GoTo ResetParameters
End If

Select Case shiftcase
Case 1
item.Value = LCase(item.Value)
Case 2
item.Value = Application.Proper(item)
Case 3
item.Value = UCase(item.Value)
End Select
Next item

ResetParameters:
Application.Calculation = xlAutomatic
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True 'Resets screen updating
Exit Sub

ErrorTrap:
If Err.Number = 13 Then Resume Next

End Sub

I just noticed on my posting for making text proper case that I left off a critical letter 's' from the word "cell" and because it is missing, it may give the reader the wrong idea about my formula. Here is the post again, but with the wording changed slightly to make it perfectly clear:

Here is a one-liner macro that will make all of the text in the selection proper case...

Sub MakeProper()
Selection = Evaluate(Replace("IF(@="""","""",PROPER(@))", "@", Selection.Address))
End Sub

And using the same structure as I did for the MakeProper macro, here is a one-liner to make all the text in the selection lower case...

Sub MakeLower()
Selection = Evaluate(Replace("IF(@="""","""",LOWER(@))", "@", Selection.Address))
End Sub

Here is one-liner macro that will also make all the text in the selected cell proper case...

Sub MakeProper()
Selection = Evaluate(Replace("IF(@="""","""",PROPER(@))", "@", Selection.Address))
End Sub