[SOLVED] Proper Case in VBA

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,

Try using PROPER instead.

Hope this helps

Sunil Jayakumar

"Mike" <[email protected]> wrote in message
news:hjR6e.8498$B93.1592@lakeread06...
> 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

Hi Mike

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

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Mike" <[email protected]> wrote in message
news:hjR6e.8498$B93.1592@lakeread06...
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"Mike" <[email protected]> wrote in message
news:hjR6e.8498$B93.1592@lakeread06...
> 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
>
>

Thanks JulieD and Sunil, the STRCONV worked great!!
Mike
"JulieD" <[email protected]> wrote in message
news:[email protected]...
> Hi Mike
>
> use the STRCONV function
> e.g.
> If Target.Column = 10 Then Target.Value =
> STRCONV(Target.Value,VBPROPERCASE)
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ...well i'm working on it anyway
> "Mike" <[email protected]> wrote in message
> news:hjR6e.8498$B93.1592@lakeread06...
>> 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
>>
>
>