VBA - Force Proper Case

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
35
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?

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,689
Try this
Code:
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
 
Last edited:

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
35
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

Code:
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

New Member
Joined
Feb 22, 2018
Messages
35
Figured it out, just needed to add "then" to the end of the statement. Works great! Thanks a lot

Tom
 

Forum statistics

Threads
1,077,976
Messages
5,337,499
Members
399,153
Latest member
Tsmith25

Some videos you may like

This Week's Hot Topics

Top