Format cell instead of using Proper()

S

Scrungie

Is there any way to format a cell so that if I enter ¨black beard¨ i
will be changed to ¨Black Beard¨ I am trying not to use the Proper(
functio
 
D

Don Guillett

You could use a worksheet_change event macro to apply proper to any word
typed.
right click sheet tab>view code paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 4 And Target.Column = 1 Then
Target.Value = StrConv(Target, vbProperCase)
'or
'Target.Value = Application.Proper(Target)
End If
End Sub
 
G

Gord Dibben

You cannot format the cell to return Proper Case.

You can use a change-event code that would convert to Proper Case when you hit
<ENTER> key.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column > 8 Then Exit Sub
'change the 8 to 1 if just want in column A
On Error GoTo ErrHandler
Application.EnableEvents = False
Target.Formula = Application.Proper(Target.Formula)
ErrHandler:
Application.EnableEvents = True
End Sub

This code as written operates on columns A through H and on every cell in
those columns.

This is worksheet code.

Right-click on your sheet tab and "View Code".

Paste in there.

Gord Dibben Excel MVP
 
Top