Change Case "Sentence Case"

[Jeffery B Paarsa]

Hi all,

Can anybody tell me how I can do a Format/Change Case "Sentence Case" on a
field I am receiving from a UserForm? I don't want to do a UpperCase on all
the characters I just want to convert the first letter of the entry to
uppercase only. User may enter space as the first Char on the field.

 

[Jay Freedman]

Hi all,

Can anybody tell me how I can do a Format/Change Case "Sentence Case"
on a field I am receiving from a UserForm? I don't want to do a
UpperCase on all the characters I just want to convert the first
letter of the entry to uppercase only. User may enter space as the
first Char on the field.

One way is to insert the string as-is into the document, and then use the
Range.Case property to make it sentence case -- this is the exact equivalent
of using Format > Change Case.

Sub foo1()
Dim myStr As String
' represents string received from userform
myStr = " this is text."

Dim myRange As Range
Set myRange = ActiveDocument.Bookmarks("bk1").Range
With myRange
.Text = myStr
.Case = wdTitleSentence
End With

ActiveDocument.Bookmarks.Add Name:="bk1", Range:=myRange
End Sub

A second way is to search the string for the first non-space character and
call the UCase function on it:

Sub foo2()
Dim myStr As String
' represents string received from userform
myStr = " this is text."

Dim nFirstLetter As Long
nFirstLetter = 1
Do While Mid$(myStr, nFirstLetter, 1) = " "
nFirstLetter = nFirstLetter + 1
Loop

Mid$(myStr, nFirstLetter, 1) = _
UCase$(Mid$(myStr, nFirstLetter, 1))

MsgBox """" & myStr & """"
End Sub

There is no exact equivalent of Format > Change Case for a string in VBA
memory; for that, the text has to be in the document body.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jay Freedman]

One way is to insert the string as-is into the document, and then use
the Range.Case property to make it sentence case -- this is the exact
equivalent of using Format > Change Case.

Sub foo1()
Dim myStr As String
' represents string received from userform
myStr = " this is text."

Dim myRange As Range
Set myRange = ActiveDocument.Bookmarks("bk1").Range
With myRange
.Text = myStr
.Case = wdTitleSentence
End With

ActiveDocument.Bookmarks.Add Name:="bk1", Range:=myRange
End Sub

A second way is to search the string for the first non-space
character and call the UCase function on it:

Sub foo2()
Dim myStr As String
' represents string received from userform
myStr = " this is text."

Dim nFirstLetter As Long
nFirstLetter = 1
Do While Mid$(myStr, nFirstLetter, 1) = " "
nFirstLetter = nFirstLetter + 1
Loop

Mid$(myStr, nFirstLetter, 1) = _
UCase$(Mid$(myStr, nFirstLetter, 1))

MsgBox """" & myStr & """"
End Sub

There is no exact equivalent of Format > Change Case for a string in
VBA memory; for that, the text has to be in the document body.

Oops, sloppy programming. In sub foo2, the Do statement should be

Do While (nFirstLetter < Len(myStr)) And _
(Mid$(myStr, nFirstLetter, 1) = " ")

just in case the string is all blanks. Otherwise you'd get an error on the
UCase call.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jeffery B Paarsa]

Hi

My string variable in on the Form but whenever I try to compile the code I
get "Compile Error Variable required - can't assign to this expression"!

With ActiveDocument
.Unprotect
Mid$(NPPkg.PLName.Text, 1, 1) = _
UCase$(Mid$(NPPkg.PLName.Text, 1, 1)) ' NPPkg is UserForm1
.Bookmarks("PFName").Range _
.InsertBefore PFName
..... etc code...
end with

Error message is not very clear when I hit the Help button.

 

[Russ]

Jeff,

Hi

My string variable in on the Form but whenever I try to compile the code I
get "Compile Error Variable required - can't assign to this expression"!

With ActiveDocument
.Unprotect
Mid$(NPPkg.PLName.Text, 1, 1) = _
UCase$(Mid$(NPPkg.PLName.Text, 1, 1)) ' NPPkg is UserForm1
.Bookmarks("PFName").Range _
.InsertBefore PFName

Did you mean '... .InsertBefore PFName.Text'?
..InsertBefore expects to see a text string and not just the name of a
bookmark.

 

[Jeffery B Paarsa]

Thanks, When I seperated it into another string it worked... But you know I
have been using this .InsertBefore PFName with out .Text on all my forms
and it works without any complaint. I guess .Text is the default I guess I
just copied from one of the codes on WMPV sites etc I don't remember that.
Thanks a lot.