+ Reply to Thread
Results 1 to 6 of 6

VBA Textbox - removing Carriage returns

  1. #1
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100

    VBA Textbox - removing Carriage returns

    I'm using a textbox in a userform, with multiline enabled.

    The text from the textbox is then sent to a cell in excel.

    The problem is that whenever Enter has been pressed within the textbox, it places a square after the text in the excel sheet. I need to stop this happening, and have no idea how to go about it.

    Any ideas?

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hello Madbloke,

    Say your cell is "A1", in VBA code you would use...

    Worksheets("Sheet1").Range("A1").Replace What:= vbCrLf, Replacement:= vbKeySpace

    Using a Worksheet Formula it would be...

    =REPLACE(A1, 1, Len(A1), " ")

    Both of these functions will remove the Carriage Return Line Feed characters and replace them with a space.

    Hope this is what you wanted,
    Leith Ross

  3. #3
    Forum Contributor
    Join Date
    09-16-2004
    Posts
    100
    Cheers! That's sorted it!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258
    Hi Madbloke,

    Glad to help.

    Thanks,
    Leith

  5. #5
    Registered User
    Join Date
    08-05-2012
    Location
    UK
    MS-Off Ver
    Excel 2010
    Posts
    53

    Re: VBA Textbox - removing Carriage returns

    I cannot get this to work in Excel 2003. I have a user form with a number of text boxes set to multiline. However, for example, a text box thatpopulates column Q shows carriage returns in each of the cells Q5:Q999. Where do I put the code and how do I adapt it to my project??

    Stu

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA Textbox - removing Carriage returns

    Stu,

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1