VBA - Force Proper Case

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
36
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,697
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
36
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
36
Figured it out, just needed to add "then" to the end of the statement. Works great! Thanks a lot

Tom
 

Forum statistics

Threads
1,078,496
Messages
5,340,711
Members
399,392
Latest member
toprak2349

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top