How to use Proper Case function? 2

[calvinb]

Sorry, I just posted this in the Access VBA category by mistake! It should be here I think since it's about VBA for Word 2003.

I want to correct the capitalizion for Surnames such as McDonald, O'Leary, etc.

I've found the following function on this forum FAQ section but I can't figure out how to make it work. I'm not very good with VBA but am slowly learning it.

Where do I put the line:

txtSurname=Propercase(txtSurname,0)

And also where do I put the main function that comes after that line?

I'm using Word 2003 and would like to select the Name that needs to be changed and have the following function correct the capitalization.

Thanks for any pointers you can give me!

Here's the code as found in this forums FAQ section:

Proper Case function to handle surnames
FAQ705-5749: Proper Case function to handle surnames
Posted: 13 Mar 05

I developed this function to correct text, such as customer names, typed into a text box on a form. Features:

-- Changes the initial letter of each word to a capital, e.g. john smith becomes John Smith
-- Optionally, changes upper case text to proper case, e.g. JOHN SMITH becomes John Smith
-- Handles 'special' surnames correctly, e.g. McDonald, O'Neill and Handley-Smith are capitalised correctly

To use this function, place it e.g. in the Lost_Focus event of a text box:

CODE
txtSurname=Propercase(txtSurname,0)

Here is the function:

CODE
Function ProperCase(strOneLine As String, intChangeType As Integer) As String

'---------------------------------------------------------------
'- This function will convert a string to Proper Case -
'- The initial letter of each word is capitalised. -
'- It will also handle special names such as O', Mc and -
'- hyphenated names -
'- if intChangeType = 1, all text is converted to proper case. -
'- e.g. 'FRED' is converted to 'Fred' -
'- if intChangeType = 0, upper case text is not converted. -
'- e.g. 'fred' becomes 'Fred', but 'FRED' remains unchanged. -
'---------------------------------------------------------------

Dim I As Integer
Dim bChangeFlag As Boolean
Dim strResult As String

'----------------------------------------------------------
'- No characters in string - nothing to do -
'----------------------------------------------------------
If Len(strOneLine) = 0 Then
ProperCase = ""
Exit Function
End If

'----------------------------------------------------------
'- Always set first letter to upper case -
'----------------------------------------------------------
strResult = UCase$(Left$(strOneLine, 1))

'----------------------------------------------------------
'- Now look at the rest of the string -
'----------------------------------------------------------
For I = 2 To Len(strOneLine)

'----------------------------------------------------------
'- If the previous letter triggered a capital, change -
'- this letter to upper case -
'----------------------------------------------------------
If bChangeFlag = True Then
strResult = strResult & UCase$(Mid$(strOneLine, I, 1))
bChangeFlag = False
'----------------------------------------------------------
'- In other cases change letter to lower case if required -
'----------------------------------------------------------
Else
If intChangeType = 1 Then
strResult = strResult & LCase$(Mid$(strOneLine, I, 1))
Else
strResult = strResult & Mid$(strOneLine, I, 1)
End If
End If

'----------------------------------------------------------
'- Set change flag if a space, apostrophe or hyphen found -
'----------------------------------------------------------
Select Case Mid$(strOneLine, I, 1)
Case " ", "'", "-"
bChangeFlag = True
Case Else
bChangeFlag = False
End Select
Next I

'----------------------------------------------------------
'- Special handling for Mc at start of a name -
'----------------------------------------------------------
If Left$(strResult, 2) = "Mc" Then
Mid$(strResult, 3, 1) = UCase$(Mid$(strResult, 3, 1))
End If

I = InStr(strResult, " Mc")
If I > 0 Then
Mid$(strResult, I + 3, 1) = UCase$(Mid$(strResult, I + 3, 1))
End If

ProperCase = strResult

End Function

 

[SkipVought]

hi,

Where do I put the line:

txtSurname=Propercase(txtSurname,0)

To use this function, place it e.g. in the Lost_Focus event of a text box:

CODE
txtSurname=Propercase(txtSurname,0)

So this FAQ is in an MS Access forum,. and refers to the Lost_Focus event of a text box. I assume that txtSurname is also a text box and depending whether it is an ActiveX control or a Forms control, a control EVENT would be appropriate.

I would use this syntax...

txtSurname.text = Propercase(txtSurname.text ,0)

The Function would be stored in the ThisDocument Code Window or a Module in your Document Project.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[calvinb]

Thanks for your reply, Skip.
I made an ActiveX textbox in a new Word document. It's just called "Textbox1" (the default name) I put your suggested code (txtSurname.text = Propercase(txtSurname.text ,0) in the LostFocus event and the functon in a module. I typed "mcdonald" in the textbox and clicked out of the textbox to trigger the lostfocus event. I got a "runtime error 424; object required" error message.
What would cause that?

 

[PHV]

It's just called "Textbox1"
So, use this code:
Textbox1.Text = Propercase(Textbox1.Text, 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

I just changed the code in the lostfocus event from "txtSurname.text = Propercase(txtSurname.text ,0" to "TextBox1.text=Propercase(TextBox1.text,0", typed in "mcdonald", clicked out to force the lostfocus event and it worked!!! I tried again with "o'brien" and it capitalized it properly also.
So the function works but now I need to tweak it a bit. On the form I use at work, there is no textbox. The names are just copied from the internet application and pasted to a blank line in the form. How can I get this function to capitalize those names? I have a button on the form with some VBA code that bolds and centers the selected names so if I could somehow just add some more code to that that would call this function.
Any suggestions?

 

[PHV]

A starting point:
Selection.Text = Propercase(Selection.Text)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[calvinb]

Thanks to both Skip & PHV. Skip told me where to put the code and that worked for textboxes but I was just copying and pasting so PHV told me to try "Selection.Text=ProperCase(Selection.Text)" and that did the trick. Thanks so much.

PS Any names starting with "O' eg. O'shea" still aren't formatting correctly but I'll continue for an answer to that later... Thanks so much!!

 

[MakeItSo]

Hi Calvin,

that's because here you don't need "proper case" but "title case":

Selection.Range.Case = wdTitleWord

;-)

Cheers,
MakeItSo

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell