Sentence Case must capitalize every first letter after a period, not just the
first letter of the sentence. The code posted fails to do that.

Try this one.

Sub optSentence_Click()
Dim myStr As String
Dim cel As Range
For Each cel In Selection
If cel.HasFormula = True Then Exit Sub
myStr = cel.Value
cel.Value = "=CapFirst(" & """" & myStr & """" & ")"
cel.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Next cel
End Sub

This Function goes along with it.

Function CapFirst(ByVal Str As String) As String
Dim aRegExp As Object, aMatch As Object, allMatches As Object
Set aRegExp = CreateObject("vbscript.regexp")
aRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
aRegExp.Global = True
Set allMatches = aRegExp.Execute(Str)
For Each aMatch In allMatches
With aMatch
Mid(Str, .firstindex + 1 + .Length - 1, 1) = _
UCase(Mid(Str, .firstindex + 1 + .Length - 1, 1))
End With
Next aMatch
CapFirst = Str
End Function


Gord Dibben MS Excel MVP


On Tue, 29 May 2007 21:45:02 -0700, PlayingToAudienceOfOne
m wrote:

Sub Change_Case()
Dim ocell As Range
Dim 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


"Roger Bell" wrote:

I know that you can use the =Proper formula for changing case from all
Capitals to Sentence. Is there a way that you can change the case from
Capitals to Sentence for a range of cells?
Thanks for any help