event macro to change text to proper case

[still learning]

Hi

I use this event macro to change the text to proper case.
I had to use an ‘s for the first time. It changed the ‘s to proper case
Example:
type in dave’s macro will change it to Dave’S after I hit enter

How do I change the macro to keep the ‘s in lower case

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
Application.EnableEvents = False
Target.Value = Application.Proper(Target.Value)
Application.EnableEvents = True
End If
End Sub

mike

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then
        Application.EnableEvents = False
        Target.Value = WorksheetFunction.Substitute(WorksheetFunction.Proper(WorksheetFunction.Substitute(Target, "'", "z-z")), "z-Z", "'")
        Application.EnableEvents = True
    End If
End Sub

 

[Rick Rothstein]

I think changing your main line of code to this should also work...

Target.Value = Replace(Application.Proper(Target.Value), "'S", "'s")

 

[still learning]

thanks mumps
perfect!!!!!

mike

 

[still learning]

opps i didn't see your reply, Rick, until i hit post reply for mumps.
thanks,

mike