Textbox Last Name Multiple Capital Letters

[CokeOrCrack]

- Within a userform, I have a textbox in which applicants type their last name
- Some last names contain multiple capital letters (e.g. "McDonald")
- The Proper Case function only capitalizes the first letter of each name and forces the remainder as lower case

Question
- Is there a way to capitalize multiple letters within the textbox, but not all the letters?

Notes
- My current code is proper case and will not allow for a capital internally

Private Sub tbLastName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    tbLastName.Value = Application.WorksheetFunction.Proper(tbLastName.Value)
    tbLastName.Value = Application.Trim(tbLastName.Value)
    If tbLastName.Value = vbNullString Then
        MsgBox "Enter Last Name"
        Cancel = True
    End If
End Sub

Thanks

OJ

 

[Scott Huish]

Use as string variable to check for the instances you want and then assign the modified string to the text box. Also there is a VBA method to substitute to Proper Case so I used that instead.

Private Sub tbLastName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s As String
    s = Application.Trim(StrConv(tbLastName.Value, vbProperCase))
    If Left(s, 2) = "Mc" Then Mid(s, 3, 1) = UCase(Mid(s, 3, 1))
    If Left(s, 3) = "Mac" Then Mid(s, 4, 1) = UCase(Mid(s, 4, 1))
    tbLastName.Value = s
    If tbLastName.Value = vbNullString Then
        MsgBox "Enter Last Name"
        Cancel = True
    End If
End Sub

 

[Rick Rothstein]

Use as string variable to check for the instances you want and then assign the modified string to the text box. Also there is a VBA method to substitute to Proper Case so I used that instead.

Private Sub tbLastName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim s As String
    s = Application.Trim(StrConv(tbLastName.Value, vbProperCase))
    If Left(s, 2) = "Mc" Then Mid(s, 3, 1) = UCase(Mid(s, 3, 1))
    If Left(s, 3) = "Mac" Then Mid(s, 4, 1) = UCase(Mid(s, 4, 1))
    tbLastName.Value = s
    If tbLastName.Value = vbNullString Then
        MsgBox "Enter Last Name"
        Cancel = True
    End If
End Sub

Handling Mac that way could lead to problems with other names. A few years back, Trenton NJ (USA), the capital city of NJ, had a mayor named Tony Mack... your code would make his last name MacK. And I am sure there are more names starting with Mac that do not have an upper case fourth letter. Personally, I don't think there is an automatic way to handle names beginning with Mac.