PDA

View Full Version : Convert a range to Proper Case



Aussiebear
06-02-2007, 10:20 PM
Is there a way to convert the contents of a range to proper case without using vba?

unmarkedhelicopter
06-03-2007, 12:57 AM
Yeah, ... =Proper(OldText) then Copy, Paste Values
It's not quick, it's not pretty but thare are no macros !!! :whistle:

mdmackillop
06-03-2007, 01:32 AM
Agreed, not without VBA, but you can fix it as you go with a worksheet event (also posted in the other thread)
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Target
If .Column = 2 Then
.Value = WorksheetFunction.Proper(.Value)
.Replace "'S", "'s"
End If
End With
Application.EnableEvents = True
End Sub

Aussiebear
06-03-2007, 02:41 AM
And if the range already exists ( 1850 rows)?

Can a conditional format do the trick?

mdmackillop
06-03-2007, 02:51 AM
Can a conditional format do the trick?
I don't see how.
You could also use a Selection Change event, and keep pressing the Down Arrow.:devil2:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Proper Target
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Proper Target
End Sub

Sub Proper(Target As Range)
Application.EnableEvents = False
With Target
If .Column = 2 Then
.Value = WorksheetFunction.Proper(.Value)
.Replace "'S", "'s"
End If
End With
Application.EnableEvents = True
End Sub