Proper Case in VBA

[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

 

[JulieD]

Hi Mike

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

 

[Sunil Jayakumar]

Hi Mike,

Try using PROPER instead.

Hope this helps

Sunil Jayakumar

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

www.ayyoo.com/dvd.html

 

[Don Guillett]

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

 

[Mike]

Thanks JulieD and Sunil, the STRCONV worked great!!
Mike