Remove leading spaces from text pasted in Texform of Userform

[Fractalis]

Hello to all,

I have a Userform with a TextBox and I need to paste to some multiline text from clipboard. The issue is that text I need to paste it has lines with many leading spaces for some lines and I want to remove those leading spaces (and trailing if there are) automatically when I paste the text within TextBox. I don't want to add a button to click after I paste the text to remove the spaces, but a way to remove those spaces when I paste it. I'm not sure if like an Event_Change.

I've tried the below code but doesn't work.

Private Sub UserForm_Click()    
    TextBox1.Value = Trim(TextBox1.Value)
End Sub

Thanks for any help.

 

[Fractalis]

Hi Rick,

Thanks for answer.

I've tried all the following events below and none of them changes the text I pasted within TextBox1.

Private Sub TextBox1_AfterUpdate()
TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub


Private Sub TextBox1_Change()
    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub


Private Sub TextBox1_Enter()
    TextBox1.Value = Trim(Replace(TextBox1.Value, vbLf & " ", vbLf & ""))
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub


Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub

To test, I'm simple pasting this text indented with 4 spaces within the VBA Editor.

Private Sub TextBox1_Change()
    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub

 

[Fluff]

What about

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Application.Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub

 

[Fractalis]

What about

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Application.Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub

Hello Fluff,

Thanks for answer.

With this input text:

 This 
     is
        some
    text

and using this code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Application.Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub

I get this output in TextBox1 when I select TextBox2

This 
 is
 some
 text

using this code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Application.Trim(Replace(TextBox1.Value, " ", ""))
End Sub

I get this output in TextBox1 when I select TextBox2

This
is
some
text

Then it seems the working solution is using Replace(TextBox1.Value, " ", "").


Thanks so much all for the help.


Best regards

 

[Akuini]

Then it seems the working solution is using Replace(TextBox1.Value, " ", "").

If you always only have 1 word in each line then it's working, but it won't work as expected if you have multiple words in one line, because it will remove all spaces.

 

[Rick Rothstein]

Then it seems the working solution is using Replace(TextBox1.Value, " ", "").

Will the text on each line alway contain single words? If so, that should work. If not, then maybe this which should cover all reasonable possibilities...

[table="width: 500"]
[tr]
	[td]TextBox1.Value = Replace(Replace(Application.Trim(Replace(TextBox1.Value, Chr(160), " ")), vbLf & " ", vbLf), " " & vbLf, vbLf)[/td]
[/tr]
[/table]

 

[Fractalis]

If you always only have 1 word in each line then it's working, but it won't work as expected if you have multiple words in one line, because it will remove all spaces.

You're rigth. Only works with single words in each line.

 

[Fractalis]

Will the text on each line alway contain single words? If so, that should work. If not, then maybe this which should cover all reasonable possibilities...

[TABLE="width: 500"]
<tbody>[TR]
[TD]TextBox1.Value = Replace(Replace(Application.Trim(Replace(TextBox1.Value, Chr(160), " ")), vbLf & " ", vbLf), " " & vbLf, vbLf)[/TD]
[/TR]
</tbody>[/TABLE]

Perfect Rick. Your solution works for lines containing several words. Yours is a general solution!!!.