Proper Case in VBA

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Good Morning All,
Using Excel XP.
Have a VBA code in a worksheet that makes the text UPPER CASE in certain
columns (see example below).
I would like to change the text to PROPER CASE but cannot do it. I've tried
changing the UCase to PCase and ProperCase but the code does not work. Any
help would be appreciated. Thank You.
Mike


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error_handler

With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Column = 10 Then Target.Value = UCase(Target.Value)
If Target.Column = 11 Then Target.Value = UCase(Target.Value)
If Target.Column = 12 Then Target.Value = UCase(Target.Value)
If Target.Column = 13 Then Target.Value = UCase(Target.Value)
If Target.Column = 14 Then Target.Value = UCase(Target.Value)
If Target.Column = 15 Then Target.Value = UCase(Target.Value)
If Target.Column = 16 Then Target.Value = UCase(Target.Value)
If Target.Column = 26 Then Target.Value = UCase(Target.Value)
If Target.Column = 28 Then Target.Value = UCase(Target.Value)
If Target.Column = 31 Then Target.Value = UCase(Target.Value)
If Target.Column = 36 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End With

Error_handler:
Resume Next

End Sub
 
Hi Mike

use the STRCONV function
e.g.
If Target.Column = 10 Then Target.Value = STRCONV(Target.Value,VBPROPERCASE)
 
Hi Mike,

Try using PROPER instead.

Hope this helps

Sunil Jayakumar

Mike said:
Good Morning All,
Using Excel XP.
Have a VBA code in a worksheet that makes the text UPPER CASE in certain
columns (see example below).
I would like to change the text to PROPER CASE but cannot do it. I've
tried changing the UCase to PCase and ProperCase but the code does not
work. Any help would be appreciated. Thank You.
Mike


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error_handler

With Target
If Not .HasFormula Then
Application.EnableEvents = False
If Target.Column = 10 Then Target.Value = UCase(Target.Value)
If Target.Column = 11 Then Target.Value = UCase(Target.Value)
If Target.Column = 12 Then Target.Value = UCase(Target.Value)
If Target.Column = 13 Then Target.Value = UCase(Target.Value)
If Target.Column = 14 Then Target.Value = UCase(Target.Value)
If Target.Column = 15 Then Target.Value = UCase(Target.Value)
If Target.Column = 16 Then Target.Value = UCase(Target.Value)
If Target.Column = 26 Then Target.Value = UCase(Target.Value)
If Target.Column = 28 Then Target.Value = UCase(Target.Value)
If Target.Column = 31 Then Target.Value = UCase(Target.Value)
If Target.Column = 36 Then Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End If
End With

Error_handler:
Resume Next

End Sub
Click to expand...

www.ayyoo.com/dvd.html
 
you might like this idea. Modify to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.HasFormula Then
Application.EnableEvents = False
Select Case Target.Column
Case Is = 3, 6, 8: Target = UCase(Target)
Case Is = 10, 11, 12, 23: Target = Application.Proper(Target)
'or strconv
Case Else
End Select
Application.EnableEvents = True
End If
End Sub
 
Back
Top