Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Surrey, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display lots of text (Office 2000)

    I'm trying to output the result of a very large text string from a Macro in to a formatted space on a sheet. I have tried a range of merged cells and a text box. Both seem to have a limit beyond which Excel gives up displaying or formatting the text.
    For merged cells, I get about 10 rows by 10 columns of Arial 10 text before fomatting fails and the rest of the text isn't displayed. For a text box, it seems to be less.
    My string can be somewhat large (> 1100 characters) which I need to display neatly across no more than 10 columns but as many rows as required.
    Has anyone seen this before or knows if there are limits? I need to keep this in Excel as part of a work tool and I'm beginning to look at splitting the string up in to bite size pieces but then I'll have a lot of fun formatting them across cells so that they look contiguous.
    Looking forward to comments and thoughts....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    Although a cell can contain 32,767 characters, only 1,024 are displayed in the cell itself, so cells (even mreged) are not suitable.
    A text box should be able to display a formatted string of over 1,100 characters without problem.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Surrey, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    Hans,
    Thanks for your reply.

    I was mistakenly using a Text shape not a Text control. It's all ok now.

  4. #4
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    I too am dealing with a large amount of text (<1100), but it is added to the text box by different people on different dates. Does anyone if it is possible for the cell / text box to both automatically resize when more data is entered into the text box? Thanks in advance!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    The following works in Excel 2002 and 2003, I don't know if it works in Excel 2000. It's no doubt possible in Excel 2007 but the steps will be different.
    - Click the border of the text box.
    - Select Format | Text Box...
    - Activate the Alignment tab.
    - Tick the check box "Automatic size".
    - Click OK.

  6. #6
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    I tried that earlier, but unless I am doing something wrong, when I set the size ot automatic, it resizes into one single LONG string - I am working in excel 2003... ?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    The user will have to press Enter to start a new line. Excel resizes the box to fit the longest line.

  8. #8
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    I must be missing something, because the only way this worked for me was making the "first entry" would have to set the size by inserting a [return], but even then, subsequent entries do not auto expand the box, nor cell
    ?
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    You haven't ticked the "Automatic size" box in the Alignment tab of Format | Text Box.
    The text box floats above the worksheet, resizing the text box won't resize the cell(s) underneath.

  10. #10
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    Yes, sorry, I forgot to do it in the sample I send you ... but when I do, this happens (one long line of text)... or if I "set" the first line with a [enter], the subsequent lines expand the box, as in the second example. I realize people try to use excel for purposes it was not intended for, but unfortunately, the powers that be have done this report in excel, so I am trying to figure out how my (un-excel) staff can use it with the least amount of frustration. Do you have any other thoughts on how to automate the cell and box size when subsequent entries are added into the box? Thanks for your assistance!
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    Excel is not the best application for this - it is designed to calculate formulas, not to handle large amounts of text.

    If the built-in Automatic size of text boxes is not suitable for you, I wouldn't use a text box. Instead, you could let users enter the text in a cell. Select the cell (or cells), then select Format | Cells... and activate the Alignment tab. Tick the check box "Wrap text", then click OK.
    The column width of the cell will remain as set by you, but with a bit of luck, the cell height will increase automatically as the user enters more text. It's not perfect though, since Excel is not a word processor. Text wrapping may be slightly off, and the automatic resizing sometimes refuses to work.

  12. #12
    Star Lounger
    Join Date
    Feb 2008
    Location
    philadelphia, Pennsylvania
    Posts
    72
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Re: Display lots of text (Office 2000)

    Unfortunately my text will end up larger than the max. char. for a cell.... I guess my users are going to have to learn how to resize to text box and cell when they are done! <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>
    Thanks again for your assistance!

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display lots of text (Office 2000)

    I'd explain the powers-that-be that this is an undesirable consequence of their decision to use Excel for something it wasn't designed for. It probably won't help now, but could prevent future mistakes...

Posting Permissions

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