Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Sub For UserForm TextBoxes & Named Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Sub For UserForm TextBoxes & Named Ranges
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. -- Regards, Tom Ogilvy "Minitman" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Sub For UserForm TextBoxes & Named Ranges
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_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 ' 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 On Tue, 8 Jul 2008 09:16:16 -0700, Tom Ogilvy wrote: 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. -- Regards, Tom Ogilvy "Minitman" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
UserForm TextBoxes | Excel Discussion (Misc queries) | |||
userform & textboxes | Excel Programming | |||
userform textboxes again | Excel Programming | |||
userform textboxes | Excel Programming |