Capitalise Names including Macs and Mcs

This routine will capitalise names. Attach it to the AfterUpdate property of a name e.g. FieldName = Surname AfterUpdate: = Proper([Surname]).

Function Proper(pstrFld As Control) As Variant
' CONVERTS first letter of each word to upper case
' RETURNS converted text value
' NOTE converts most proper names correctly e.g. McKinvoy, O'Connor
' -----------------------------------------------------------------------------------------------
Dim intArraySize As Integer
Dim intArrayPos As Integer
Dim strReturnVal As String

If IsNull(pstrFld) Then
Proper = Null
Exit Function
End If

intArraySize = Len(Trim(pstrFld)) ' set size of array

pstrFld = LCase(pstrFld) ' set all chrs to lowercase

ReDim strArray(intArraySize) ' size array to hold field

For intArrayPos = 1 To intArraySize ' fill the array with the field characters
strArray(intArrayPos) = Mid$(pstrFld, intArrayPos, 1)
Next intArrayPos

strReturnVal = UCase(strArray(1)) ' upper case the first character

If strArray(1) = "M" Then ' check if first name starts with Mac or Mc
If intArraySize > 1 Then ' make sure array is not 1 character only
If strArray(2) = "a" And strArray(3) = "c" Then
strArray(4) = UCase(strArray(4)) ' upper case 1st letter after Mac
End If
If strArray(2) = "c" Then
strArray(3) = UCase(strArray(3)) ' upper case 1st letter after Mc
End If
End If
Else
If strArray(1) = "O" Then ' check if name starts with O'
If strArray(2) = "'" Then
strArray(3) = UCase(strArray(3)) ' uppercase character after O'
End If
End If
End If

For intArrayPos = 2 To intArraySize ' go through the remaining letters
' check for separators , - or .
If strArray(intArrayPos) = " " Or strArray(intArrayPos) = "-" Or strArray(intArrayPos) = "." Then
strArray(intArrayPos + 1) = UCase(strArray(intArrayPos + 1)) ' upper case the character after the separator
' check for Mac or Mc again
If strArray(intArrayPos + 1) = "M" Then
If strArray(intArrayPos + 2) = "a" And strArray(intArrayPos + 3) = "c" Then
strArray(intArrayPos + 4) = UCase(strArray(intArrayPos + 4)) ' uppercase character after Mac
End If
If strArray(intArrayPos + 2) = "c" Then
strArray(intArrayPos + 3) = UCase(strArray(intArrayPos + 3)) ' uppercase character after Mc
End If
End If
If strArray(intArrayPos) = "O" Then ' check if name starts with O'
If strArray(intArrayPos + 1) = "'" Then
strArray(intArrayPos + 2) = UCase(strArray(intArrayPos + 2)) ' uppercase character after O'
End If
End If
End If
' add the next current character to the answer
strReturnVal = strReturnVal + strArray(intArrayPos)

Next intArrayPos
' return the answer

pstrFld = strReturnVal
End Function

Take a little care where you use it or you may find it will save machine as MacHine! Other than that - enjoy!