automatically change text case on entry
 

I use the following code to automatically change the case of a range of cells
to uppercase in Excel, which works fine.

However I need some other cells in the same sheet to automatically change to
Proper Case. Is there a way of adapting the following code to enable upper
and proper case?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
ElseIf Not Intersect(Target, Range("A1,B2,J10")) Is Nothing Then
With Target
.Value = Application.Proper(.Value)
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


In article ,
"John Davies" wrote:

I use the following code to automatically change the case of a range of cells
to uppercase in Excel, which works fine.

However I need some other cells in the same sheet to automatically change to
Proper Case. Is there a way of adapting the following code to enable upper
and proper case?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hi JE McGimspey

That works fine. Thanks for your help

"John Davies" wrote:

I use the following code to automatically change the case of a range of cells
to uppercase in Excel, which works fine.

However I need some other cells in the same sheet to automatically change to
Proper Case. Is there a way of adapting the following code to enable upper
and proper case?

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("H1,M5,O11,F7")) Is Nothing Then
With Target
.Value = UCase(.Value)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub