Proper case using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can I force 'proper' case for text within excel
The following code forces uppercase, however I would like only the first
letters of each word capitalized: ie Joe Bloggs.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("E3:F200")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

Can this be tweaked to what I woulf like?
 
This may work:-
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True

End Sub

Mike
 
Back
Top