Overcoming issues with vbProperCase

[Big Lar]

So that Users do not have to capitalize the entries made into UserForm TextBoxes, I’ve incorporated these lines of code.

ws.Cells(iRow, 1) = StrConv(TextBox1.Text, vbProperCase)
ws.Cells(iRow, 2) = StrConv(TextBox2.Text, vbProperCase)
ws.Cells(iRow, 3) = StrConv(TextBox2.Text, vbProperCase) & " " & StrConv(TextBox1.Text, vbProperCase

However, this creates a problem…because surnames such as McDonald and LaDeaue become Mcdonald and Ladeaue in the destination database.

I use a 3^rd-party application (where the names are properly capitalized) to build an event attendee list. My code then adds data from the database to the attendee list. The differences in capitalization are causing “Type mismatch” errors in this process:

For Each Cl In wsAttendees.Range("A2", wsAttendees.Range("A" & Rows.Count).End(xlUp))
         sKey = Trim(Cl.Value)
        
If .Item(sKey)(2) = "" Then

I’m inclined to eliminate the vbProperCase coding but, I’m wondering if there might be another solution?

 

[JLGWhiz]

Just a comment, that I would not use ProperCase where names are involved. Especially if the owners of the names will be viewing them.

 

[mikerickson]

How does the third party handle a name like "k.d. lang"?

Names either have to be dealt with by hand, or, if automatically, by users who realize that the capitalization is going to be off sometimes.

 

[Big Lar]

Thanks for confirming my suspicions.
Fortunately, "k.d. lang", "50 Cent", or even "Big Lar" are names that aren't associated with my project!