Capitalizing Just a Surname

@J. WoolleynOops! 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.nnFor 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 VanhaelennI posted a similar event procedure on the same day here: https://excelribbon.tips.net/T012550_Using_an_Input_Mask.htmlnTherefore, I noticed some issues with your procedure:n1. You need to disable events before changing Target; otherwise, the event procedure will repeat many times (46) for that Target.n2. 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.n3. 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.nHere is an improved version of your procedure:nnPrivate Sub Worksheet_Change(ByVal Target As Range)n    Dim NameCells As Range, cell As Range, valu As String, iComma As Integern    Set NameCells = Range("A:A") 'adjust as requuiredn    For Each cell In Targetn        If Not (Application.Intersect(cell, NameCells) Is Nothing _n            Or cell.HasFormula) Thenn            valu = cell.Valuen            iComma = InStr(valu, ",")n            If iComma > 0 Thenn                valu = UCase(Left(valu, iComma - 1)) & Mid(valu, iComma)n                Application.EnableEvents = Falsen                    cell.Value = valu 'low risk of errorn                Application.EnableEvents = Truen            End Ifn        End Ifn    Next cellnEnd Sub

Here is a macro that does the capitalization at the moment you enter the name (for column A):nnPrivate Sub Worksheet_Change(ByVal Target As Range)nIf Left(Target.Address, 3) <> "$A$" Then Exit SubnDim iComma As IntegerniComma = InStr(Target(1), ",")nIf iComma = 0 Then Exit SubnTarget = UCase(Left(Target(1), iComma - 1)) & Mid(Target(1), iComma)nEnd SubnnChange "($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+.