That's a neat idea to give more control to the typist (usually me!).

But if this were in a worksheet_change event (as opposed to a macro run on
demand), I add the LTrim() to clean up my "flagged" entry, too.



Jerry Park wrote:

walt wrote:
Any ideas how to change all(!) Mc names from Mcwhatever to McWhatever ?

TIA Walt


I use an onchange event to format proper names, and the McWhatever
construction is the main problem. But you also need to allow for other
constructions (like de Beers, DePriest, etc.). I've found a simple way
to allow the user of the form to bypass the formating function for non
standard names -- add a space to the end:

If Not (Mid(Target.Text, Len(Target.Text), 1) = " ") Then
' If ends with a space, don't format.
NewText = String(1, " ") + LCase(Target.Text)
NewText = Application.WorksheetFunction.Substitute(NewText, " mc", " mc~")
NewText = Application.WorksheetFunction.Proper(NewText)
NewText = Application.WorksheetFunction.Substitute(NewText, " Mc~", " Mc")
NewText = LTrim(NewText)
Target = NewText
End If

--

Dave Peterson