Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Write multi-line text box to merged cells (Excel 2003)

    I need more brain power than I am capable of today.... Attached is a sample workbook with one form in it and code behind it that functions the same as my real workbook. The form has fields to enter names, address, city, etc. The address field is a multi-line textbox that lets you enter multiple lines with the Enter key. All works fine until I go to write this data to a cell on a worksheet. I write the data from the form to the worksheet because I want to keep 'n' sets of this customer data for later retrieval.

    How can I avoid seeing the little 'square' boxes in the text written to the merged cells that represent CR/LF from the form's textbox?

    I need to be able to do this both ways - from the form's textbox to the worksheet's merged cells, and from the worksheet back to the form's textbox (because at form init, I read the worksheet data to use as the form's default data).

    I'm using merged cells on the worksheet because I need to allow for multiple lines in the address field (for other odd address entries like mail stop, etc.). Can I do this w/o merged cells, I don't know (they mimic the multi-line textbox).

    Thnx,
    Deb

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Write multi-line text box to merged cells (Excel 2003)

    You can enter a newline in Excel using Alt-Enter, this puts vbLf (character code 10) into the cell.
    The box you are seeing represents character 10 followed by character 13, which is vbLf followed by vbCr.

    In your code, you can do this by replacing the vbCr in the text box with a null string, before copying it to the worksheet, just use
    <code> rng.Offset(1, 0).Value = Replace(Me.txtAddr, vbCr, "") </code>
    instead of
    <code> rng.Offset(1, 0).Value = Me.txtAddr</code>

    When you have made this change, you can merge those 3 cells in your workbook, and it all seems to work fine.

    StuartR

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Write multi-line text box to merged cells (Excel 2003)

    Thanks for the advice. I knew about Alt+Enter but know what I should do to transfer the merged cell's data to a textbox on a form. I want the textbox to contain the same thing as the merged cell (the same line breaks). I'll use what you showed to replace the incoming text from the merged cells before writing it to the form's textbox. Since the user can enter stuff in the textbox too (on the form) I then need to write that text to the merged cells on the worksheet (when they press OK button on the form). The sheet is read-only so it's this pop-up form that they use to enter the information. It was only this merged area that gave me problems.

    Thnx,
    Deb

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Write multi-line text box to merged cells (Excel 2003)

    Here is a modified version of your workbook, I have made the following changes...
    <UL><LI>Merged the cells where you store the address so that they are on one line only (on both worksheets)
    <LI>Changed the offsets to compensate for the merged cells
    <LI>Used REPLACE to remove all vbCR characters from the multiline text box before inserting in the worksheet
    <LI>Used Autofit to resize the address line to the correct height for the text it contains[/list]Does this behave the way you want?

    StuartR

Posting Permissions

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