VBA - Force Proper Case

[tomexcel1]

Hi All,

I have the following piece of VBA code that ensures if a name is entered in column 14 then then it makes sure its proper case or changes it. However when you paste in more then one line in to column 14 i get the error: Run-Time Error '13' Type Mismatch. Can anyone think how to stop this happening?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)'Force Proper Case in Name Column
 
Application.EnableEvents = False
 
If Target.Column = 14 Then
 
Target = StrConv(Target, vbProperCase)
 
End If
 
Application.EnableEvents = True
 
  
End Sub

Thanks
Tom

 

[mikerickson]

Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)'Force Proper Case in Name Column
    Dim oneCell As Range
    Application.EnableEvents = False
   
    With Target
        If Not Application.Intersect(.Cells, Columns(14)) Is Nothing
            For Each oneCell in Application.Intersect(.Cells, Columns(14))
                oneCell = StrConv(CStr(oneCell.Value), vbProperCase)
            Next oneCell
        End If
     End With

    Application.EnableEvents = True
End Sub

 

[tomexcel1]

Hey,

Thanks for your reply, I've tried your code but i get an error on the highlighted line. Error is Compile Error: Syntax Error

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Force Proper Case in Name Column
    Dim oneCell As Range
    Application.EnableEvents = False
   
    With Target
[B][COLOR=#ff0000]        If not Application.Intersect(.Cells, Columns(14)) Is Nothing[/COLOR][/B]
            For Each oneCell In Application.Intersect(.Cells, Columns(14))
                oneCell = StrConv(CStr(oneCell.Value), vbProperCase)
            Next oneCell
        End If
     End With


    Application.EnableEvents = True
End Sub

Thanks Again
Tom

 

[tomexcel1]

Figured it out, just needed to add "then" to the end of the statement. Works great! Thanks a lot

Tom