Capitalize first letter of first word only

[sharky12345]

Is there a way I can capitalize the first letter of the first word only in a Userform Textbox, rather than every word?

Better still, how about what I would call true propercase, where the first word of each sentence is capitalized, rather than the usual propercase which in my view is not accurate because it capitalizes every word.

 

[sharky12345]

Thanks guys - found this though which has done the trick for anyone else who is tinkering......

Capitilize First Letter & After Full Stop/Period in TextBox

 

[Rick Rothstein]

Also, there exist Sentence Case, where Proper is directed toward Proper names.
see https://msdn.microsoft.com/en-us/library/office/microsoft.office.core.msotextchangecase.aspx

I do not believe the material at that link has anything to do with Excel worksheet function nor VBA functions.

 

[SpillerBD]

well its in the Office library so its one of the Microsoft frustration. I have the library and my VBE can see the function, reference the library, but can't use it? Why Microsoft? Why!

 

[Rick Rothstein]

Thanks guys - found this though which has done the trick for anyone else who is tinkering......

Capitilize First Letter & After Full Stop/Period in TextBox

That code is not "perfect"...

First, type a couple of letters and then move the text cursor back to the beginning of the text and type a lower case letter... the originally upper cased letter, which is now the second character, will remain upper cased.

Second, that code will not handle multiple paragraphs (created after hitting the Enter Key, assuming you set the TextBox's EnterKeyBehavior to True) correctly... the first letter of the second and subsequent paragraphs will remain lower case.

I have not tested that code further to see if it has any other anomalies or not. I think I agree with the statement Dave Hawley made in the linked thread, namely, I would use the Exit event instead. For the function I posted in Message #9, the Exit event procedure code would look like this...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  TextBox1.Text = SentenceCase(TextBox1.Text)
End Sub

 

[Rick Rothstein]

That code is not "perfect"...

Two more places the code at the link you posted fails...

First, copy a group of upper case text from somewhere, then run/show the UserForm, type some text in, then paste what you copied earlier into the text at any location... the upper case text you copied remains upper case after being pasted in.

Second, type a single letter (works with more letters, but a single letter is more dramatic), then say you realize it was the wrong letter, so you press the backspace key to delete it so that you can type the correct letter... Boom!... the program crashes instead.

It is not easy to write a perfect, on-the-fly parser as I believe I have demonstrated in this and my previous posts.

 

[SpillerBD]

I believe Rick R's code covers the OP's request.
I did find (though I didn't test it) an old thread that has a more flexible code that covers the diferent CASE options....
https://social.msdn.microsoft.com/Forums/office/en-US/1883dbbf-342e-4345-90f4-f9920ae9e2cb/what-is-the-syntax-for-using-msotextchangecase?forum=exceldev#2ec98533-57a1-4972-8154-c82092aed36d

I'll probably file Rick's code for later, and not the one in the above link.

 

[sharky12345]

Guys - I'm very grateful and will test the suggestions.

Thank you!