Remove leading spaces from text pasted in Texform of Userform

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
310
Office Version
  1. 2019
Platform
  1. Windows
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.

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

Thanks for any help.
 
Last edited:
Hi Rick,

Thanks for answer.

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

Code:
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.
Code:
Private Sub TextBox1_Change()
    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub
 
Last edited:
Upvote 0

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
What about
Code:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Application.Trim(Replace(TextBox1.Value, Chr(160), " "))
End Sub
 
Upvote 0
What about
Code:
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:
Code:
 This 
     is
        some
    text


and using this code:
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

Code:
This 
 is
 some
 text




using this code:
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
Code:
This
is
some
text


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


Thanks so much all for the help.


Best regards
 
Upvote 0
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.
 
Upvote 0
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...
Code:
[table="width: 500"]
[tr]
	[td]TextBox1.Value = Replace(Replace(Application.Trim(Replace(TextBox1.Value, Chr(160), " ")), vbLf & " ", vbLf), " " & vbLf, vbLf)[/td]
[/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
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...
Code:
[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!!!.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top