Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Capitalization | Excel Discussion (Misc queries) | |||
Add-in name capitalization? | Excel Programming | |||
Capitalization | Excel Discussion (Misc queries) | |||
Capitalization? | Excel Worksheet Functions | |||
Capitalization | Excel Discussion (Misc queries) |