Sentence Capitalization - Revisited
 

Greetings,

I thought this was completed, boy was I wrong!

The problem is solved for the UserForm TextBoxes. However, I can't
seem to get that code to work for worksheet merged cell named ranges.

I am not even sure where to put the code.

So the question is, how do I get these four merged cell named ranges
to format with the first letter of each sentence being capitalized?

Any help will be appreciated.

-Minitman

Sentence Capitalization - Revisited
 

Post the code you are trying.

"Minitman" wrote:

Greetings,

I thought this was completed, boy was I wrong!

The problem is solved for the UserForm TextBoxes. However, I can't
seem to get that code to work for worksheet merged cell named ranges.

I am not even sure where to put the code.

So the question is, how do I get these four merged cell named ranges
to format with the first letter of each sentence being capitalized?

Any help will be appreciated.

-Minitman

Sentence Capitalization - Revisited
 

Hey JLGWhiz,

Thanks for the reply.

Not knowing where else to place the code, I added a Type(4) to this
Worksheet_Change event code:
__________________________________________________ ____________________

'Rick Rothstein's code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim S As String
Dim Types(1 To 4) As Range
Dim myStr As String, m As Object
If Target.Count 1 Then Exit Sub
If Target.Row 30 Then Exit Sub
' MapsCo Format
Set Types(1) = Range("pfCell_24")
' Telephone Format
Set Types(2) = _
Union(Range("pfCell_19"), Range("pfCell_21"))
For X = 37 To 55 Step 2
Set Types(2) = _
Union(Types(2), Range("pfCell_" & X))
Next
' Extension Format
Set Types(3) = _
Union(Range("pfCell_20"), Range("pfCell_22"))
For X = 38 To 56 Step 2
Set Types(3) = _
Union(Types(3), Range("pfCell_" & X))
Next

'Memo Fields Format
Set Types(4) = _
Range("pfCell_23")
For X = 78 To 80
Set Types(4) = _
Union(Types(4), Range("pfCell_" & X))
Next
S = Target.Value
If Target.Count 1 Or Len(S) = 0 Or _
Intersect(Target, _
Union(Range("pfCell_19:pfCell_22"), _
Range("pfCell_23:pfCell_24"), _
Range("pfCell_37:pfCell_56"), _
Range("pfCell_78:pfCell_80"))) Is Nothing Then _
Exit Sub
For X = 1 To Len(S)
If Target.Address = _
Range("pfCell_24").Address Then
If UCase(Left(S, 3)) = "MAP" Then S = _
Mid(S, 4)
If Mid(S, X, 1) Like "[!0-9a-zA-Z]" Then _
Mid(S, X, 1) = " "
ElseIf Mid(S, X, 1) Like "[!0-9]" Then _
Mid(S, X, 1) = " "
End If
Next
S = Replace(S, " ", "")

For X = 1 To 4
If Not Intersect(Target, Types(X)) Is Nothing _
Then Exit For
Next
Select Case X
Case 1 'MapsCo Formatting
If Len(S) = 2 Then
S = Format(S, "##")
ElseIf S Like _
"###[a-zA-Z][a-zA-Z][a-zA-Z]##" Then
S = Format(S, "!Map @@@@ \<@@-@@\")
Else
S = "<??" & Target.Value & "<??"
End If
Case 2 'Telephone Format
If Len(S) = 2 Then
S = Format(S, "00")
ElseIf Len(S) = 7 Then
S = Format(S, "000-0000")
ElseIf Len(S) = 10 Then
S = Format(S, "(000) 000-0000")
Else
Exit Sub
End If
Case 3 'Extension Format
S = Format(S, "0")
Case 4 'Memo Field Format
myStr = LCase(S)
With CreateObject("VBScript.RegExp")
.Pattern = "(^|(\.|\?|!)\s)[a-z]\S+"
.Global = True
If .test(myStr) Then
For Each m In .Execute(myStr)
myStr = _
WorksheetFunction.Replace(myStr, _
m.FirstIndex + 1, m.Length, _
StrConv(m.Value, 3))
Next
Target.Value = myStr
End If
End With
End Select

On Error GoTo EndIt
Application.EnableEvents = False
' Target.Value

EndIt:
Application.EnableEvents = True

End Sub
__________________________________________________ ____________________

The first three types work. The Type(4) does not. When I enter a
Type(4) merged cell named range that is supposed to be affected by
this code, something strange happens. All of the first letter of each
sentence (except the very first character) are capitalized with the
next space before the Worksheet_Change event kicks in which is when I
hit Enter or Tab and leave the cell.

I have no clue as to what this part of the code does:

__________________________________________________ ____________________

With CreateObject("VBScript.RegExp")
.Pattern = "(^|(\.|\?|!)\s)[a-z]\S+"
.Global = True
If .test(myStr) Then
For Each m In .Execute(myStr)
myStr = _
WorksheetFunction.Replace(myStr, _
m.FirstIndex + 1, m.Length, _
StrConv(m.Value, 3))
__________________________________________________ ____________________

I was able to determine (using MsgBox's) what the value of
..test(myStr) is. It came up False for what it's worth.

This is an adaptation of the code that jindon from Ozgrid gave me.

But I am wondering if this code is a smoke screen for what is actually
happening, since most of the capitalization is being done before the
Worksheet_Change event is kicking in. But I do not have a clue where
it is happening or how!!!

For comparison purposes here is the code that is working in the
UserForm:

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++
'Code for the local TextBox to run the SentenceCaps code
Private Sub TextBox23_Change()
If Not Len(TextBox23.Value) = 0 Then SentenceCaps "TextBox23"
End Sub
__________________________________________________ ____________________
'The code is located in a general module
Public Sub SentenceCaps(sFrom As String)
Dim lLowerChar As Long, _
lUpperChar As Long, _
lStop As Long, _
lLoop As Long, _
lLen As Long, _
lInnerLoop As Long
Dim strText As String, _
strOld As String, _
strNew As String

lLowerChar = 97
lUpperChar = 65
lStop = 33
strText = NewCustForm.Controls(sFrom)
lLen = Len(strText)
strText = _
UCase(Left(strText, 1)) _
& Right(strText, Len(strText) - 1)
If lLen 1 Then
For lLoop = 1 To 25
For lInnerLoop = 1 To 3
strOld = _
Chr(lStop) & " " & Chr(lLowerChar)
strNew = _
Chr(lStop) & " " & Chr(lUpperChar)
strText = _
Replace(strText, strOld, strNew)
strOld = Chr(lStop) & Chr(lLowerChar)
strNew = _
Chr(lStop) & " " & Chr(lUpperChar)
strText = _
Replace(strText, strOld, strNew)
If lStop = 33 Then
lStop = 46
ElseIf lStop = 46 Then
lStop = 63
Else
lStop = 33
End If
Next lInnerLoop
lLowerChar = lLowerChar + 1
lUpperChar = lUpperChar + 1
Next lLoop
End If
NewCustForm.Controls(sFrom) = strText
End Sub
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++

Any help will be greatly appreciated.

-Minitman


On Wed, 9 Jul 2008 20:05:00 -0700, JLGWhiz
wrote:

Post the code you are trying.

"Minitman" wrote:

Greetings,

I thought this was completed, boy was I wrong!

The problem is solved for the UserForm TextBoxes. However, I can't
seem to get that code to work for worksheet merged cell named ranges.

I am not even sure where to put the code.

So the question is, how do I get these four merged cell named ranges
to format with the first letter of each sentence being capitalized?

Any help will be appreciated.

-Minitman

Sentence Capitalization - Revisited
 

This macro will capitalize the first word. Just click on the merged cell
that you want to capitalize and run the macro. If your data uses more than
50 columns
you will need to change all the 50's to a higher number. If this is not what
you want, the formula for making the first word a capital is :-
"=UPPER(LEFT(RC[50]))&RIGHT(LOWER(RC[50]),LEN(RC[50])-1)" Hope this
helps.


Sub FirstLetterCapital()
Application.ScreenUpdating = False
ActiveCell.Range("A1:D1").Select
Selection.Copy
ActiveCell.Offset(0, 50).Range("A1:D1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -50).Range("A1:D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=UPPER(LEFT(RC[50]))&RIGHT(LOWER(RC[50]),LEN(RC[50])-1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 50).Range("A1:D1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -50).Range("A1:D1").Select
Application.ScreenUpdating = True
End Sub


"JLGWhiz" wrote in message
...
Post the code you are trying.

"Minitman" wrote:

Greetings,

I thought this was completed, boy was I wrong!

The problem is solved for the UserForm TextBoxes. However, I can't
seem to get that code to work for worksheet merged cell named ranges.

I am not even sure where to put the code.

So the question is, how do I get these four merged cell named ranges
to format with the first letter of each sentence being capitalized?

Any help will be appreciated.

-Minitman

Sentence Capitalization - Revisited
 

If you want to do it for more than one merged cell, write the macro to loop
through the range of cells you require changing

Sentence Capitalization - Revisited
 

Hey Skinman,

Thanks for the reply.

My confusion over why the sentence caps were working except for the
first sentence has been enlightened. I had accidentally turned on
AutoCorrectCapitalize first letter of sentences. Strangely enough,
since it did not see any spaces in front of the first letter, it just
ignored it.

I was able to get jindons vbscript code to do the first letter (it was
supposed to do all of the sentences not just the first). With the
AutoCorrect active and jindon's code in the Worksheet_Change event,
the named ranges is capitalizing the way it should, with some minor
glitches.

Your idea of using a helper column does have some merit, just not in
this case.

Again, thanks for the effort.

-Minitman


On Thu, 10 Jul 2008 15:02:09 +1000, "Skinman"
wrote:

This macro will capitalize the first word. Just click on the merged cell
that you want to capitalize and run the macro. If your data uses more than
50 columns
you will need to change all the 50's to a higher number. If this is not what
you want, the formula for making the first word a capital is :-
"=UPPER(LEFT(RC[50]))&RIGHT(LOWER(RC[50]),LEN(RC[50])-1)" Hope this
helps.


Sub FirstLetterCapital()
Application.ScreenUpdating = False
ActiveCell.Range("A1:D1").Select
Selection.Copy
ActiveCell.Offset(0, 50).Range("A1:D1").Select
ActiveSheet.Paste
ActiveCell.Offset(0, -50).Range("A1:D1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=UPPER(LEFT(RC[50]))&RIGHT(LOWER(RC[50]),LEN(RC[50])-1)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 50).Range("A1:D1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveCell.Offset(0, -50).Range("A1:D1").Select
Application.ScreenUpdating = True
End Sub


"JLGWhiz" wrote in message
...
Post the code you are trying.

"Minitman" wrote:

Greetings,

I thought this was completed, boy was I wrong!

The problem is solved for the UserForm TextBoxes. However, I can't
seem to get that code to work for worksheet merged cell named ranges.

I am not even sure where to put the code.

So the question is, how do I get these four merged cell named ranges
to format with the first letter of each sentence being capitalized?

Any help will be appreciated.

-Minitman