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