Results 1 to 4 of 4

Thread: VBA - Force Proper Case

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - Force Proper Case

    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

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,645
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: VBA - Force Proper Case

    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 by mikerickson; Oct 21st, 2019 at 11:23 AM.

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Force Proper Case

    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
            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
    Thanks Again
    Tom

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - Force Proper Case

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

    Tom

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •