Remove leading spaces from text pasted in Texform of Userform

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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 & "")

  <!--[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

New Member
Joined
Dec 17, 2003
Messages
35
Office Version
365, 2019, 2016, 2013, 2010
Platform
Windows
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

Active Member
Joined
Oct 11, 2011
Messages
252
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

Active Member
Joined
Oct 11, 2011
Messages
252
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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

Active Member
Joined
Oct 11, 2011
Messages
252
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:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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:

Fractalis

Active Member
Joined
Oct 11, 2011
Messages
252
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,056
Office Version
2010
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,102,778
Messages
5,488,814
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top