Remove Spaces in the Middle of Textbox VBA

[amoverton2]

Still learning VBA...

I have userform with a textbox to enter LAST,FIRST NAME. I want no spaces between the last name and first name so it matches with other data in the workbook for other things.

Examples: Good: JORDAN,MICHAEL, Bad: JORDAN, MICHAEL.

I am looking for a VBA code that if a person does enter JORDAN, MICHAEL, an event (AfterUpdate or Exit) removes the space between last and first name. I figured out how to convert it to all uppercase if someone puts it in lowercase but now I need to remove any spaces. Everything I've seen is either beginning or end of the text string, nothing in the middle. If there is a thread out there please point me there and I'll close this one up quickly.

Thanks for your time.

 

[dmt32]

Hi,
try

Private Sub TextBox1_AfterUpdate()
     Me.TextBox1.Value = Replace(Me.TextBox1.Value, " ", "")
End Sub

Dave

 

[Fluff]

Another option in-case you can have names like van Vleuten, Annemiek

Private Sub TextBox1_AfterUpdate()
     Me.TextBox1.Value = Replace(Me.TextBox1.Value, ", ", ",")
End Sub

 

[amoverton2]

both options work perfectly! ty!

 

[Gokhan Aycan]

What if the user enters more than 1 space in there?

Maybe a for next loop with count of spaces that executes the above code would be better. Code not tested.

Edit: Added the trim part.

Private Sub TextBox1_AfterUpdate()

    Dim i as Integer
    Dim str as String
    Dim Count as Integer

    str = Trim (Me.TextBox1.Value)
    Count = Len (str) - Len (Replace (str, " ", ""))

    For i = 1 to Count
        str = Replace (str, ", ", ",")
    Next i

    Me.TextBox1.Value = str

End Sub

 

[dmt32]

both options work perfectly! ty!

you are welcome - glad we were able to help
We appreciate your feedback

Dave