Textbox First Name

[CokeOrCrack]

- I have a Userform that contains a Textbox where an applicant enters their first name
- Some applicants use two names in their first name with no hyphen (e.g. "Rose Ann")

Question
- I want the textbox to kick out spaces prior and following the name
- I also want to remove double or more spaces in the middle

Notes
- I have removed all spaces in my Last Name textbox (due to last names containing hyphens rather than spaces)
- The code is as follows:

Private Sub tbLastName_Change()
    tbLastName.Value = Application.WorksheetFunction.Proper(tbLastName.Value)
    tbLastName.Text = Replace(tbLastName.Text, " ", "")
End Sub

- Line two is unrelated to this thread
- I'm assuming the code will be more complex for the First Name textbox, or is it similar?

Thanks

OJ

 

[Rick Rothstein]

- I have a Userform that contains a Textbox where an applicant enters their first name
- Some applicants use two names in their first name with no hyphen (e.g. "Rose Ann")

Question
- I want the textbox to kick out spaces prior and following the name
- I also want to remove double or more spaces in the middle

Notes
- I have removed all spaces in my Last Name textbox (due to last names containing hyphens rather than spaces)
- The code is as follows:

Private Sub tbLastName_Change()
    tbLastName.Value = Application.WorksheetFunction.Proper(tbLastName.Value)
    tbLastName.Text = Replace(tbLastName.Text, " ", "")
End Sub

First, you can remove leading, trailing and multiple spaces by using Excel's TRIM function instead of VB's Trim function...

FirstName = Application.Trim(FirstNameSource)

where you would replace FirstNameSource with the actual source containing the first name value (which I presume would be your TextBox) and you would replace FirstName with whatever is to receive the modified first name text (the same or different TextBox, a cell reference, a variable, etc.).

Second, you should not assume last names cannot have spaces in them. Before retiring, I worked with a man whose last name was Della Rossa.

 

[CokeOrCrack]

Rick

Good catch. Thinking on it now, a few international associates of mine this past year did have spaces rather than hyphens in their last name.

I have plugged your code in and it prevents spaces in front and behind, but it also prevents spaces (even just one) in the middle.

Private Sub tbFirstName_Change()
    tbFirstName.Value = Application.WorksheetFunction.Proper(tbFirstName.Value)
    tbFirstName.Value = Application.Trim(tbFirstName.Value)
End Sub

The above is what I have plugged in.

Is there a way to allow a space in the middle?

Thanks

 

[Rick Rothstein]

Rick

Good catch. Thinking on it now, a few international associates of mine this past year did have spaces rather than hyphens in their last name.

I have plugged your code in and it prevents spaces in front and behind, but it also prevents spaces (even just one) in the middle.

Private Sub tbFirstName_Change()
    tbFirstName.Value = Application.WorksheetFunction.Proper(tbFirstName.Value)
    tbFirstName.Value = Application.Trim(tbFirstName.Value)
End Sub

There is no way that the code line I gave you can remove all internal spaces like you are suggesting... it operates exactly like Excel's TRIM function. You must have other event code somewhere that is interfering with what you are doing.

 

[CokeOrCrack]

I am wrong again. The above code does allow for no more than one consecutive space internally. The problem is that while typing a string, the space that becomes internal is initially at the end; so I need to type the name with no space first and move the cursor internally to add the space.

I'm assuming this cannot be fixed do to the sequence?

 

[CokeOrCrack]

Rick

I changed my first line to update the textbox upon exiting it. Now the textbox works as intended.

Private Sub tbFirstName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    tbFirstName.Value = Application.WorksheetFunction.Proper(tbFirstName.Value)
    tbFirstName.Value = Application.Trim(tbFirstName.Value)
End Sub

Thanks for the help!

Post: Thinking more on spacing in last names, ignorance appears to have slapped me in the face. My own last name had a space in it but it was removed when my ancestors immigrated nearly a century ago.