Proper case textbox with returns.

foseco · #1 07-03-2009, 05:25 PM

Hi. I have a spreadsheet using a textbox to enter notes into a cell from a user. I would like the text to be formatted in the correct case for sentences taking into account multiple line/carriage returns in the textbox. I have the following code I found on another website which seems to work on the first line but will not captalize the following lines.

Code:

Private Sub CommandButton1_Click()
  Dim myStr As String, m As Object, myPatn As String
  myStr = TextBox1.Value
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^|[!\.\?](\s+)?)\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
    End If
    myPtn = "(jan(uary)?|feb(ruary)?|mar(ch)?|apr(il)?|jun(e)?|jul(y)?|aug(st)?|sep(tember)?|oct(ober)?|nov(ember)?|dec(ember)?)"
    .Pattern = "\b(" & myPtn & "|(sun|mon|tue(s)?|wed(nes)?|thu(rs)?|fri|sat(ur)?)(day)?)/b" '<- Weekday/Month  names
    .Global = True
    .IgnoreCase = 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
    End If
    TextBox1.Value = myStr
  End With
  Sheets("List Items").Range("H1") = TextBox1.Value
  End
End Sub

**Leith Ross** · #2 07-03-2009, 07:33 PM

Hello foseco,

My guess is you don't need all the pattern recognition in this script for what you are doing. It would be easier to provide you with an answer if you tell us more about what needs to be capitalized. Posting your workbook would be a good idea.

foseco · #3 07-04-2009, 08:55 AM

Thanks for that. I have attached a copy of the spreadsheet. Its an automated shopping list for my Mother. The notes are used to add sundry items that she doesn't want on the main lists. I just want it to capitalize the first letter of each line from the notes she enters so it's tidier when it's printed. I think you're right about the pattern, she won't be entering thing like proper names or months or days of the week. I did notice one thing. The routine works if we add a full stop to the end of each line but trying to get my Mother to remember to do that would be like trying to knit fog.

If you click the Notes button, you will see some items we have already added.

Thanks

Foseco

foseco · #4 07-04-2009, 09:28 AM

Actually, I'm lying. I tried it again and it only capitalizes the first line no matter what is at the end.

**Leith Ross** · #5 07-04-2009, 03:52 PM

Hello foseco,

This macro will capitalize the first word of each sentence. I could not write this for your workbook because I don't have Excel 2007. As far as I know, all the code I used is compatible with 2007. Copy this code into a VBA module.

Code:

Function CapitalizeFirstWord(ByVal S1 As String) As String

  Dim RE As Object
  Dim S As Variant
  Dim S2 As String
    
    Set RE = CreateObject("VBScript.RegExp")
    RE.Pattern = "^[a-z]"
        
      For Each S In Split(S1, vbLf)
        If RE.Test(S) Then
           S2 = S2 & RE.Replace(S, UCase(Left(S, 1))) & vbLf
        End If
      Next S
      
   Set RE = Nothing
   CapitalizeFirstWord = S2
   
End Function

Welcome to the Excel Forum

Forum Statistics