Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: Remove leading spaces from text pasted in Texform of Userform

  1. #1
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Remove leading spaces from text pasted in Texform of Userform

    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 by Fractalis; Aug 22nd, 2019 at 07:52 PM.

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,141
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Try this code, it will remove all extra spaces in the textbox1.
    But I am not sure where to put it. If you put it in ‘TextBox1_Change’ then that will cause a problem: you cannot manually type a space in it because it will be deleted automatically.


    Code:
    TextBox1.Text = Replace(WorksheetFunction.Trim(TextBox1.Text), vbLf & " ", vbLf & "")
    
      

  3. #3
    New Member
    Join Date
    Dec 2003
    Location
    Alpharetta, GA
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Why are you using UserForm_Click()?? Use this instead:

    Private Sub BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Trim(TextBox1.Value)
    End Sub

    This way, when you tab out of the multi-line text box after you paste, or if you click on a button, the before_update event removes the spaces with no other actions.

  4. #4
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Quote Originally Posted by bbotzong View Post
    Why are you using UserForm_Click()?? Use this instead:

    Private Sub BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Value = Trim(TextBox1.Value)
    End Sub

    This way, when you tab out of the multi-line text box after you paste, or if you click on a button, the before_update event removes the spaces with no other actions.
    Excellent bbotzong. Thanks so much for the solution shared.

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Hi bbotzong,

    Now I was testing your solution but doesn't work. The text pasted is not changed. The spaces are not removed.

    Why would be missing?

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,141
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Did you try my code in post #2 ?
    You can place the code in:
    Private Sub TextBox1_Exit, so it will run when you exit the textbox.
    or in Private Sub TextBox1_DblClick , so it will run when you double click the textbox,

  7. #7
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Hello Akuini,

    Thanks for your answer.

    I've tried with DblClick and doesn't do anything.
    Code:
    Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        TextBox1.Text = Trim(TextBox1.Text)
    End Sub
    and with Exit and doesn't change anything either.

    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        TextBox1.Text = Trim(TextBox1.Text)
    End Sub
    Last edited by Fractalis; Aug 23rd, 2019 at 02:13 PM.

  8. #8
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,141
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    That's not the code I was talking about, use my code in post #2 .
    Code:
    Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        TextBox1.Text = Replace(WorksheetFunction.Trim(TextBox1.Text), vbLf & " ", vbLf & "")
    End Sub
    Last edited by Akuini; Aug 23rd, 2019 at 02:20 PM.

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    252
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Quote Originally Posted by Akuini View Post
    That's not the code I was talking about, use my code in post #2 .
    Is the same, nothing happens using Exit nor DblClick with Replace an Trim.

    Code:
    Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        TextBox1.Text = Replace(WorksheetFunction.Trim(TextBox1.Text), vbLf & " ", vbLf & "")
    End Sub

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,315
    Post Thanks / Like
    Mentioned
    93 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Remove leading spaces from text pasted in Texform of Userform

    Quote Originally Posted by Fractalis View Post
    Hi The text pasted is not changed. The spaces are not removed.
    I think maybe your "spaces" (ASCII 32) are actually "non-breaking spaces" (ASCII 160) which usually comes from copying text from the web (although there are other sources for it). Instead of this...

    TextBox1.Value = Trim(TextBox1.Value)

    Try this instead...

    TextBox1.Value = Trim(Replace(TextBox1.Value, Chr(160), " "))

    in whichever event procedure you feel is appropriate.
    Last edited by Rick Rothstein; Aug 23rd, 2019 at 02:25 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •