Always Change Input Text to Proper

[John]

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

 

[Harald Staff]

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]

Thanks Harald, works perfectly

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