Capitalize first letter of first word only

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

henrik2h

Board Regular
Joined
Aug 25, 2008
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
? When I use Proper() it does exactly what you want? Capital letter when new sentence.
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
Well this capitalizes every word;

Code:
txtquestion.Value = StrConv(txtquestion.Value, vbProperCase)
 

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
=Proper()
Capitalises the 1st character of each word.
Code:
[TABLE="width: 273"]
<tbody>[TR]
[TD]where the first word of each sentence is capitalized,[/TD]
[/TR]
[TR]
[TD]Where The First Word Of Each Sentence Is Capitalized,[/TD]
[/TR]
</tbody>[/TABLE]
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I get a compile error with this - what am I doing wrong?

Code:
txtquestion.Value = Proper()

Just realised that the previous post was just confirming what I had stated earlier.........
 

henrik2h

Board Regular
Joined
Aug 25, 2008
Messages
139
Office Version
  1. 2019
Platform
  1. Windows
Ok, on my computer it doesn't, perhaps a regional setting then. Sorry
 

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

No worries - thanks anyway!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,163
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
@sharky12345

First off, you are talking about "sentence" case, not "proper" case and VB code... second, gaz_chops was giving you a worksheet function solution, so the two of you are not talking about the same thing. There is no native sentence case function in either the worksheet functions or VB's functions, so we have to write one. Here is my idea for such a function (which can be called by other VB code or used as a UDF directly on a worksheet)...

Code:
Function SentenceCase(ByVal S As String) As String
  Dim X As Long, Z As Long, Paragraphs() As String, Sentences() As String
  Paragraphs = Split(S, vbLf)
  For X = 0 To UBound(Paragraphs)
    Sentences = Split(Paragraphs(X), ". ")
    For Z = 0 To UBound(Sentences)
      Sentences(Z) = UCase(Left(Sentences(Z), 1)) & LCase(Mid(Sentences(Z), 2))
    Next
    Paragraphs(X) = Join(Sentences, ". ")
  Next
  SentenceCase = Join(Paragraphs, vbLf)
End Function

For your needs, just pass it the contents of the TextBox and assign wherever you need to. For example, this CommandButton event code would change TextBox1's entry by the user into sentence case directly within TextBox1...

Code:
Private Sub CommandButton1_Click()
  TextBox1.Text = SentenceCase(TextBox1.Text)
End Sub
 

Forum statistics

Threads
1,136,768
Messages
5,677,623
Members
419,707
Latest member
Anna vib

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top