Auto Proper Case in a cell

[still learning]

Hi
Is there a way to have a Row (C) to default to proper case?
Roc (C) will always be a string.
I was thinking of using an event change macro but anything above the new entry would already be in proper case and wouldn't the wait time be long when i'm down to, say C900?
I tried Data Validation, but it only alerts if the the string is not in proper case. it doesn't change the string.
What I use now is the following macro:

Sub Propercase()     
Dim c As Range
For Each c In Selection
    c.Value = StrConv(c.Value, vbProperCase)
Next c
End Sub

After I type the string, I have an icon on the ribbon to change the case. I would like to have it change when i leave the cell

mike

 

[Michael M]

Try using this...it needs to be pasted into the Sheet module

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

 

[still learning]

Hi Mike
Perfect.
Happy Holidays and stay safe

mike

 

[Rick Rothstein]

Try using this...it needs to be pasted into the Sheet module

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

I would suggest replacing the red-highlighted line of code with this one...

Target.Value = Application.Proper(Target.Value)

as Excel's Proper function is better than VBA's version. For example, it handles text after dashes or opening parentheses better.

 

[still learning]

Thanks Rick
Happy holidays and stay safe, too

mike