Public Sub For UserForm TextBoxes & Named Ranges

[Minitman]

Greetings,

I made a public sub to be accessed by four UserForm TextBoxes. It
works well. But now I would like for this same public sub to be
accessed from worksheet named ranges as well. I'm not sure if it is
possible, debug did not like my first attempt!

Here is the working public sub code:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'This sub is in a General Module
Public Sub SentenceCaps(sFrom As String)
Dim lLowerChar As Long, _
lUpperChar As Long, _
lStop As Long
lLen As Long, _
lInnerLoop As Long, _
lLoop As Long
Dim strOld As String, _
strNew As String _
strText As String

lLowerChar = 97
lUpperChar = 65
lStop = 33
strText = Me.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

Me.Controls(sFrom) = strText
End Sub
______________________________________________________________
'This sub on the UserForm code section
Private Sub TextBox1()
SentenceCaps "TextBox1"
End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

This code works on the UserForm. The TextBoxes are labeled TextBox1
though TextBox4

It is when I try to use it on the worksheet form as well, with it's
named ranges, that it errors out on the Me.Controls() commend (it
suddenly does not like "Me."). The named ranges are NamedRange_1
though NamedRange_4

The names of the controls and named ranges are arbitrary. The fact
that they are different seems to be giving me problems.

Here is my first failed attempt:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
Select Case Len(sFrom)
'UserForm TextBox names are "TextBox00" or a character count of 9
Case 9
strText = Me.Controls(sFrom) '<<<<<<<<<<<<<<<<<<<
'Worksheet named ranges are "NamedRange_00" or a character count of 13
Case 13
strText = Range(sFrom)
End Select
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
Select Case Len(sFrom)
'UserForm TextBox name is "TextBox00" or character count is 9
Case 9
Me.Controls(sFrom) = strText
Worksheet named range is "NamedRange_00" or character count is 13
Case 13
Range(sFrom) = strText
End Select

End Sub
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The only difference between the two is the use of the select case to
try and differentiate

 

[Tom Ogilvy]

the object "ME" is undefined in a general module, so it isn't clear where
your code is actually located.

Worksheets don't have a controls child. A named range would be in the
Names collection.

 

[Minitman]

Hey Tom,

Thanks for the reply.

Since that is the case, how do I modify this code:
_____________________________________________________________
Select Case Len(sFrom)
Case 9
strText = NewCustForm.Controls(sFrom) 'This one was easy
Case 13
strText = ws2.Range(sFrom) 'But not so with this one
End Select
_____________________________________________________________

With your observation about "Me", I simply replaced "Me" with the name
of the UserForm. That portion now works. Thanks!!!

However, coming through the Worksheet_Change event is a different
story altogether!!!

I was getting an upper case formatted letter as the first letter of
each sentence except for the very first letter. But the strange thing
is that when I remmed out the SentenceCaps line - nothing changed!!!

In other words, the Worksheet_Change event was not sending the
contents of the cell over to the general module to be modified. When
I went looking for the code that was affecting the text, I found
nothing!

Now I'm really confused. How is the sheet doing as much as it is
without any code?

After looking at all of the code, I could not see anything that should
affect the capitalization of anything.

Here is the Worksheet_Change sub, Maybe someone out there will see
something that I missed (I hope):
____________________________________________________________
'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

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_19fCell_22"), _
Range("pfCell_23fCell_24"), _
Range("pfCell_37fCell_56"), _
Range("pfCell_78fCell_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
' SentenceCaps Target.Value
End Select

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

EndIt:
Application.EnableEvents = True

End Sub
____________________________________________________________

Any insights, thoughts, links, or suggestions will be greatly
appreciated.

-Minitman