Thread: Proper case textbox with returns.

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

Would anyone have any ideas on how this can be altered to incorporate carriage returns?

Thanks for any help.

Foseco

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

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