I am trying to write some code that will always change Text once entered to
the proper case. The text in question is First Name, Last Name (entered in
cells O7:O24). I am using the following but am getting an error. What am I
doing wrong?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("O7:O24")) Is Nothing Then
Application.EnableEvents = False
.Value = Proper(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub


Thanks

Hi

Proper is a worksheet function, not a VBA function.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("O7:O24")) Is Nothing Then
Application.EnableEvents = False
.Formula = StrConv(.Formula, vbProperCase)
Application.EnableEvents = True
End If
End If
End With
End Sub

(I've changed .value to .formula to allow formula entries in the cells.
Change back if it's unwanted.)

HTH. Best wishes Harald


"John" skrev i melding
...
I am trying to write some code that will always change Text once entered
to
the proper case. The text in question is First Name, Last Name (entered in
cells O7:O24). I am using the following but am getting an error. What am I
doing wrong?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("O7:O24")) Is Nothing Then
Application.EnableEvents = False
.Value = Proper(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub


Thanks

Thanks Harald, works perfectly


"Harald Staff" wrote in message
...
Hi

Proper is a worksheet function, not a VBA function.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("O7:O24")) Is Nothing Then
Application.EnableEvents = False
.Formula = StrConv(.Formula, vbProperCase)
Application.EnableEvents = True
End If
End If
End With
End Sub

(I've changed .value to .formula to allow formula entries in the cells.
Change back if it's unwanted.)

HTH. Best wishes Harald


"John" skrev i melding
...
I am trying to write some code that will always change Text once entered
to
the proper case. The text in question is First Name, Last Name (entered
in
cells O7:O24). I am using the following but am getting an error. What am
I
doing wrong?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("O7:O24")) Is Nothing Then
Application.EnableEvents = False
.Value = Proper(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub


Thanks