VBA - Force Proper Case

tomexcel1

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

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,363
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
40
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
40
Figured it out, just needed to add "then" to the end of the statement. Works great! Thanks a lot

Tom
 

Watch MrExcel Video

Forum statistics

Threads
1,096,426
Messages
5,450,360
Members
405,607
Latest member
griffindor2020

This Week's Hot Topics

Top