Proper Case
 

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

Proper Case
 

This may come in handy

Sub ChangeCase() 'Don Guillett
Application.ScreenUpdating = False
Dim r As Range
nCase = UCase(InputBox("Enter U for UPPER" & Chr$(13) & " L for
lower" & Chr$(13) & " Or " & Chr$(13) & " P for Proper", "Select
Case Desired"))
Select Case nCase
Case "L"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = LCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = LCase(r.Value)
End If
Next

Case "U"
For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = UCase(r.Formula)
'R.Formula = R.Value
Else
r.Value = UCase(r.Value)
End If
Next
Case "P"

For Each r In Selection.Cells
If r.HasFormula Then
r.Formula = Application.Proper(r.Formula)
'R.Formula = R.Value
Else
r.Value = StrConv(r.Value, vbProperCase)
End If
Next
End Select
Application.ScreenUpdating = True
End Sub

--
Don Guillett
SalesAid Software

"Roger Bell" wrote in message
...
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

Proper Case
 

Here is a UDF to do it

Private Function SentenceCase(ByVal para As String) As String
Dim oRegExp As Object
Dim oMatch As Object
Dim oAllMatches As Object

para = LCase(para)
Set oRegExp = CreateObject("VBScript.RegExp")
oRegExp.Pattern = "^[a-z]|\.( )*[a-z]"
oRegExp.Global = True
Set oAllMatches = oRegExp.Execute(para)
For Each oMatch In oAllMatches
With oMatch
Mid(para, .FirstIndex + 1 + .Length - 1, 1) = _
UCase(Mid(para, .FirstIndex + 1 + .Length - 1, 1))
End With
Next oMatch
SentenceCase = para
End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Roger Bell" wrote in message
...
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

Proper Case
 

Roger

Proper case is not sentence case.

This Is Proper Case.

Sentence case would have a capital at the beginning of each sentence only.

You want which?

Here is a macro for sentence case.

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


Gord Dibben MS Excel MVP

On Tue, 24 Apr 2007 04:24:03 -0700, 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

Proper Case
 

Here's one for Proper Case.

Sub optProper_Click()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = Application.Proper(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord

On Tue, 24 Apr 2007 16:52:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Roger

Proper case is not sentence case.

This Is Proper Case.

Sentence case would have a capital at the beginning of each sentence only.

You want which?

Here is a macro for sentence case.

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


Gord Dibben MS Excel MVP

On Tue, 24 Apr 2007 04:24:03 -0700, 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

Proper Case
 

Hi,

No.

You cannot do this by entering the formula. You have to write a code.

Challa Prabhu


"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

Proper Case
 

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

Proper Case
 

Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

"PlayingToAudienceOfOne" m
wrote in message ...
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

Proper Case
 

Certainly looks like it, and gave the same answer in most cases <g

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nick Hodge" wrote in message
...
Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

"PlayingToAudienceOfOne"
m wrote in message
...
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

Proper Case
 

I did find one with a function reply but other than that it was all the
same!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

"Bob Phillips" wrote in message
...
Certainly looks like it, and gave the same answer in most cases <g

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nick Hodge" wrote in message
...
Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

"PlayingToAudienceOfOne"
m wrote in message
...
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

Proper Case
 

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

Proper Case
 

And his Sentence Case code is incorrect to start with.


Gord

On Wed, 30 May 2007 20:40:47 +0100, "Nick Hodge"
wrote:

I did find one with a function reply but other than that it was all the
same!

Proper Case
 

3 years.

"Nick Hodge" wrote:

Wow

Did you find every occurrence in the last 5 years!

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/

"PlayingToAudienceOfOne" m
wrote in message ...
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

Proper Case
 

Make that "her" code.

"Gord Dibben" wrote:

And his Sentence Case code is incorrect to start with.


Gord

On Wed, 30 May 2007 20:40:47 +0100, "Nick Hodge"
wrote:

I did find one with a function reply but other than that it was all the
same!

Proper Case
 

Excuse me.

I assume since you do not have a gender-based name you must get a lot of that.


Gord

On Sat, 2 Jun 2007 13:40:01 -0700, PlayingToAudienceOfOne
m wrote:

Make that "her" code.

"Gord Dibben" wrote:

And his Sentence Case code is incorrect to start with.


Gord

On Wed, 30 May 2007 20:40:47 +0100, "Nick Hodge"
wrote:

I did find one with a function reply but other than that it was all the
same!