Capitalizing Just a Surname

@J. Woolley
Oops! I know that with the Worksheet_Change event Sub you often must disable events but as I made this UDF in a hurry, I forgot. :-( This is not an excuse because VBA doesn’t “forget” anything and I should not post a macro without thoroughly testing it.

For your remarks 2 and 3. You are of course right but one is not supposed to enter a formula in a column that must have only names in it. The purpose of the UDF is to enable you to enter new names in a list all lower case, one by one, transforming the surname automatically in upper case.

@Willy Vanhaelen
I posted a similar event procedure on the same day here: https://excelribbon.tips.net/T012550_Using_an_Input_Mask.html
Therefore, I noticed some issues with your procedure:
1. You need to disable events before changing Target; otherwise, the event procedure will repeat many times (46) for that Target.
2. If you enter a formula in column A that results in text with a comma, that formula will be replaced by a constant equal to the modified result.
3. If you copy N rows of names and paste them into column A, when the first name includes a comma it gets modified and duplicated in N rows of column A; otherwise, the N rows are pasted without change.
Here is an improved version of your procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NameCells As Range, cell As Range, valu As String, iComma As Integer
    Set NameCells = Range("A:A") 'adjust as requuired
    For Each cell In Target
        If Not (Application.Intersect(cell, NameCells) Is Nothing _
            Or cell.HasFormula) Then
            valu = cell.Value
            iComma = InStr(valu, ",")
            If iComma > 0 Then
                valu = UCase(Left(valu, iComma - 1)) & Mid(valu, iComma)
                Application.EnableEvents = False
                    cell.Value = valu 'low risk of error
                Application.EnableEvents = True
            End If
        End If
    Next cell
End Sub

Here is a macro that does the capitalization at the moment you enter the name (for column A):

Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) <> "$A$" Then Exit Sub
Dim iComma As Integer
iComma = InStr(Target(1), ",")
If iComma = 0 Then Exit Sub
Target = UCase(Left(Target(1), iComma - 1)) & Mid(Target(1), iComma)
End Sub

Change "($A$)" according to the column letter(s) you are using.

I believe the LET function is in Excel 2021 and later. Since Excel 365 is always later, I like to reference Excel 2021+ as shorthand when discussing such functions. And I encourage anyone how is serious about Excel to upgrade to Excel 2021+.