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.

 

[Akuini]

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.

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

  <!--[if gte mso 9]><xml>  <o:OfficeDocumentSettings>   <o:RelyOnVML/>   <o:AllowPNG/>  </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:TrackMoves/>   <w:TrackFormatting/>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:DoNotPromoteQF/>   <w:LidThemeOther>EN-US</w:LidThemeOther>   <w:LidThemeAsian>X-NONE</w:LidThemeAsian>   <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>    <w:SplitPgBreakAndParaMark/>    <w:EnableOpenTypeKerning/>    <w:DontFlipMirrorIndents/>    <w:OverrideTableStyleHps/>   </w:Compatibility>   <m:mathPr>    <m:mathFont m:val="Cambria Math"/>    <m:brkBin m:val="before"/>    <m:brkBinSub m:val="--"/>    <m:smallFrac m:val="off"/>    <m:dispDef/>    <m:lMargin m:val="0"/>    <m:rMargin m:val="0"/>    <m:defJc m:val="centerGroup"/>    <m:wrapIndent m:val="1440"/>    <m:intLim m:val="subSup"/>    <m:naryLim m:val="undOvr"/>   </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="false"   DefSemiHidden="false" DefQFormat="false" DefPriority="99"   LatentStyleCount="371">   <w:LsdException Locked="false" Priority="0" QFormat="true" Name="Normal"/>   <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 1"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 2"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 3"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 4"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 5"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 6"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 7"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 8"/>   <w:LsdException Locked="false" Priority="9" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="heading 9"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index 9"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 1"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 2"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 3"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 4"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 5"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 6"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 7"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 8"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" Name="toc 9"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footnote text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="header"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footer"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="index heading"/>   <w:LsdException Locked="false" Priority="35" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="caption"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="table of figures"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="envelope address"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="envelope return"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="footnote reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="line number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="page number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="endnote reference"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="endnote text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="table of authorities"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="macro"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="toa heading"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Bullet 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Number 5"/>   <w:LsdException Locked="false" Priority="10" QFormat="true" Name="Title"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Closing"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Signature"/>   <w:LsdException Locked="false" Priority="1" SemiHidden="true"    UnhideWhenUsed="true" Name="Default Paragraph Font"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="List Continue 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Message Header"/>   <w:LsdException Locked="false" Priority="11" QFormat="true" Name="Subtitle"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Salutation"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Date"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text First Indent"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text First Indent 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Note Heading"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Body Text Indent 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Block Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Hyperlink"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="FollowedHyperlink"/>   <w:LsdException Locked="false" Priority="22" QFormat="true" Name="Strong"/>   <w:LsdException Locked="false" Priority="20" QFormat="true" Name="Emphasis"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Document Map"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Plain Text"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="E-mail Signature"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Top of Form"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Bottom of Form"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal (Web)"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Acronym"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Address"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Cite"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Code"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Definition"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Keyboard"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Preformatted"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Sample"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Typewriter"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="HTML Variable"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Normal Table"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="annotation subject"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="No List"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Outline List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Simple 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Classic 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Colorful 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Columns 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Grid 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 4"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 5"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 6"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 7"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table List 8"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table 3D effects 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Contemporary"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Elegant"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Professional"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Subtle 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Subtle 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 1"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 2"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Web 3"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Balloon Text"/>   <w:LsdException Locked="false" Priority="39" Name="Table Grid"/>   <w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"    Name="Table Theme"/>   <w:LsdException Locked="false" SemiHidden="true" Name="Placeholder Text"/>   <w:LsdException Locked="false" Priority="1" QFormat="true" Name="No Spacing"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading"/>   <w:LsdException Locked="false" Priority="61" Name="Light List"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 1"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 1"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 1"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 1"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 1"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 1"/>   <w:LsdException Locked="false" SemiHidden="true" Name="Revision"/>   <w:LsdException Locked="false" Priority="34" QFormat="true"    Name="List Paragraph"/>   <w:LsdException Locked="false" Priority="29" QFormat="true" Name="Quote"/>   <w:LsdException Locked="false" Priority="30" QFormat="true"    Name="Intense Quote"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 1"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 1"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 1"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 1"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 1"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 1"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 1"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 1"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 2"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 2"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 2"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 2"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 2"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 2"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 2"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 2"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 2"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 2"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 2"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 2"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 2"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 2"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 3"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 3"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 3"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 3"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 3"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 3"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 3"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 3"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 3"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 3"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 3"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 3"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 3"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 3"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 4"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 4"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 4"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 4"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 4"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 4"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 4"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 4"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 4"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 4"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 4"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 4"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 4"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 4"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 5"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 5"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 5"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 5"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 5"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 5"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 5"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 5"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 5"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 5"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 5"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 5"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 5"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 5"/>   <w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 6"/>   <w:LsdException Locked="false" Priority="61" Name="Light List Accent 6"/>   <w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 6"/>   <w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 6"/>   <w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 6"/>   <w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 6"/>   <w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 6"/>   <w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 6"/>   <w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 6"/>   <w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 6"/>   <w:LsdException Locked="false" Priority="70" Name="Dark List Accent 6"/>   <w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 6"/>   <w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 6"/>   <w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 6"/>   <w:LsdException Locked="false" Priority="19" QFormat="true"    Name="Subtle Emphasis"/>   <w:LsdException Locked="false" Priority="21" QFormat="true"    Name="Intense Emphasis"/>   <w:LsdException Locked="false" Priority="31" QFormat="true"    Name="Subtle Reference"/>   <w:LsdException Locked="false" Priority="32" QFormat="true"    Name="Intense Reference"/>   <w:LsdException Locked="false" Priority="33" QFormat="true" Name="Book Title"/>   <w:LsdException Locked="false" Priority="37" SemiHidden="true"    UnhideWhenUsed="true" Name="Bibliography"/>   <w:LsdException Locked="false" Priority="39" SemiHidden="true"    UnhideWhenUsed="true" QFormat="true" Name="TOC Heading"/>   <w:LsdException Locked="false" Priority="41" Name="Plain Table 1"/>   <w:LsdException Locked="false" Priority="42" Name="Plain Table 2"/>   <w:LsdException Locked="false" Priority="43" Name="Plain Table 3"/>   <w:LsdException Locked="false" Priority="44" Name="Plain Table 4"/>   <w:LsdException Locked="false" Priority="45" Name="Plain Table 5"/>   <w:LsdException Locked="false" Priority="40" Name="Grid Table Light"/>   <w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark"/>   <w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful"/>   <w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 1"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 1"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 1"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 1"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 1"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 2"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 2"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 2"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 2"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 2"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 3"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 3"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 3"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 3"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 3"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 4"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 4"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 4"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 4"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 4"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 5"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 5"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 5"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 5"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 5"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="46"    Name="Grid Table 1 Light Accent 6"/>   <w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 6"/>   <w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 6"/>   <w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 6"/>   <w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/>   <w:LsdException Locked="false" Priority="51"    Name="Grid Table 6 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="52"    Name="Grid Table 7 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/>   <w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/>   <w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 1"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 1"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 2"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 2"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 3"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 3"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 4"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 4"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 5"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 5"/>   <w:LsdException Locked="false" Priority="46"    Name="List Table 1 Light Accent 6"/>   <w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/>   <w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/>   <w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/>   <w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/>   <w:LsdException Locked="false" Priority="51"    Name="List Table 6 Colorful Accent 6"/>   <w:LsdException Locked="false" Priority="52"    Name="List Table 7 Colorful Accent 6"/>  </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable 	{mso-style-name:"Table Normal"; 	mso-tstyle-rowband-size:0; 	mso-tstyle-colband-size:0; 	mso-style-noshow:yes; 	mso-style-priority:99; 	mso-style-parent:""; 	mso-padding-alt:0cm 5.4pt 0cm 5.4pt; 	mso-para-margin-top:0cm; 	mso-para-margin-right:0cm; 	mso-para-margin-bottom:8.0pt; 	mso-para-margin-left:0cm; 	line-height:107%; 	mso-pagination:widow-orphan; 	font-size:11.0pt; 	font-family:"Calibri","sans-serif"; 	mso-ascii-font-family:Calibri; 	mso-ascii-theme-font:minor-latin; 	mso-hansi-font-family:Calibri; 	mso-hansi-theme-font:minor-latin;} </style> <![endif]-->

 

[bbotzong]

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.

 

[Fractalis]

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.

 

[Fractalis]

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?

 

[Akuini]

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,

 

[Fractalis]

Hello Akuini,

Thanks for your answer.

I've tried with DblClick and doesn't do anything.

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.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Text = Trim(TextBox1.Text)
End Sub

 

[Akuini]

That's not the code I was talking about, use my code in post #2 .

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

 

[Fractalis]

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.

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

 

[Rick Rothstein]

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.