Might I suggest that converting "Josh Smith" to "Smith, Josh"
with a comma would be less ambiguous, AND you would not
have to worry about macros reentering data, or use of F2 then Enter.
Modify Bob Phillips' earlier code in this thread to test for a comma.
If there is a comma present there would be no interest to change.
If also means you can later manually change an entry to "Smith, G. Josh"..
without it getting messed up.
Also since the change only takes place if there is no comma, and this is specifically
for people's names you will be able to type in a name in all lower case like "josh smith"
but provide correct capitalization if needed for names like "Ron de Bruin" or "Angus McDuff"
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPos As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
If InStr(.Value, ",") = 0 Then '-- test for comma
iPos = InStr(1, Target.Value, " ")
If iPos > 0 Then
.Value = Right(.Value, Len(.Value) - iPos) & _
", " & Left(.Value, iPos - 1)
End If
If LCase(.Value) = .Value Then .Value = Application.Proper(.Value)
End If '--added from test for comma
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub
Wasn't mentioned but this is an Event Macro is installed differently than standard macros
Right click on worksheet name, View code, insert the above code.
Notice that Bob has a recovery for an on error condition for Application.EnableEvents
if you mess up this type of macro and have turned off events then a macro like this
would no longer work. You can fix that by typing that line into the Intermediate Window
in the Visual Basic Editor.
Application.EnableEvents = True
more information on Event macros and specifically to reenable after error
http://www.mvps.org/dmcritchie/excel/event.htm#problems
Another way of doing this would be to use a standard macro invoked manually
that does the check for a comma and makes the change if there is not commas.
There are lots of examples for this. (probably everyone with an Excel page)
http://www.mvps.org/dmcritchie/excel/join.htm#lastname
http://www.cpearson.com/excel/FirstLast.htm
Please include your name either with your email address or in your signature line.