Remove leading spaces from text pasted in Texform of Userform
Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

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

  1. #11
    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 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 by Fractalis; Aug 23rd, 2019 at 01:50 PM.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,858
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

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

    What about
    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
        TextBox1.Value = Application.Trim(Replace(TextBox1.Value, Chr(160), " "))
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    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 Fluff View Post
    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

  4. #14
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,933
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    3 Thread(s)

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

    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.

  5. #15
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,091
    Post Thanks / Like
    Mentioned
    92 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
    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:
    TextBox1.Value = Replace(Replace(Application.Trim(Replace(TextBox1.Value, Chr(160), " ")), vbLf & " ", vbLf), " " & vbLf, vbLf)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  6. #16
    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
    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.

  7. #17
    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 Rick Rothstein View Post
    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:
    TextBox1.Value = Replace(Replace(Application.Trim(Replace(TextBox1.Value, Chr(160), " ")), vbLf & " ", vbLf), " " & vbLf, vbLf)
    Perfect Rick. Your solution works for lines containing several words. Yours is a general solution!!!.
    Last edited by Fractalis; Aug 23rd, 2019 at 03:12 PM.

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
  •