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
fCell_22"), _
Range("pfCell_23
fCell_24"), _
Range("pfCell_37
fCell_56"), _
Range("pfCell_78
fCell_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
1").Select