VBA - Force Proper Case

tomexcel1

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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:
Upvote 0
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
 
Upvote 0
Figured it out, just needed to add "then" to the end of the statement. Works great! Thanks a lot

Tom
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top