Sentence Capitalizatio

[Minitman]

Greetings,

How Do I use "Application.AutoCorrect.CorrectSentenceCap". I can't
seem to get it to work!

Or is there a different way to get the contents of a TextBox to
convert to correct sentence structure with the first letter of each
sentence to be capitalized and the rest lower case?

Any help will be appreciated.

-Minitman

 

[Gary Keramidas]

this may do what you want

Sub test()
With Range("A1")
.Value = LCase(.Value)
.Value = UCase(Left(.Value, 1)) & Mid(.Value, 2, Len(.Value))
End With
End Sub

 

[Gary Keramidas]

this will do the same thing,
With Range("A1")
.Value = UCase(Left(.Value, 1)) & Mid(LCase(.Value), 2, Len(.Value))
End With

 

[Minitman]

Hey Gary,

Thanks for the reply.

If I understand your code, it is forcing the format of cell A1 into
the 1st character being uppercase, and everything else to be
lowercase, is that correct?

I think a better description of my problem is in order

First, these TextBoxes are on a UserForm, not a sheet.
Second, the TextBoxes are being used as memo fields. There could be
many sentences in 1 TextBox not just one.

I was given code snippet as a possible solution awhile back, but I
didn't check it out:

TextBox1.Text = Application.AutoCorrect.CorrectSentenceCap = True

This appears to only turn on the AutoCorrect.CorrectSentenceCap
property according to the MS help file. Other then that, I don't
know!

Any other ideas?

-Minitman

 

[Gary Keramidas]

somebody posted an answer last year for you. did it work?

http://www.ozgrid.com/forum/showthread.php?t=77385

 

[Rick Rothstein \(MVP - VB\)]

The following code should do what you want. Note that only the period,
exclamation point and question mark are assumed to be end of sentences; if
you want other symbols to mark the end of a sentence, you will have to add
the appropriate Replace function calls (follow the structure I used for the
other symbols if you have to do this).

Dim X As Long
Dim TBoxText As String
Dim Lines() As String
TBoxText = TextBox1.Text
TBoxText = Replace(Replace(TBoxText, "? ", "?."), "! ", "!.")
TBoxText = Replace(Replace(TBoxText, ". ", "."), vbLf, vbLf & ".")
Lines = Split(TBoxText, ".")
For X = 0 To UBound(Lines)
Lines(X) = UCase(Left(Lines(X), 1)) & Mid(LCase(Lines(X)), 2)
Next
TBoxText = Join(Lines, ".")
TBoxText = Replace(Replace(TBoxText, "?.", "? "), "!.", "! ")
TBoxText = Replace(Replace(TBoxText, ".", ". "), vbLf & ".", vbLf)
TextBox1.Text = TBoxText

Rick

 

[Minitman]

Hey Gary,

Thank you for finding that thread! I had forgotten about it and I
haven't been on that site in a while. I was having trouble with my
subject names.

And yes, Dave's solution did finally work, once we got the number of
spaces after the punctuation settled. I still don't understand
Jindon's solution, though.

 

[Minitman]

Hey Rick,

Thanks for the reply.

I tried your code, but could not get it to work properly.

Gary's reminder of my old post on Ozgrid last year did contain an
answer that works, I had forgotten about it.

I think this problem is solved.

Thanks everyone.

-Minitman