Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying large amounts of text (2000 SP3)

    Not sure if this can be done. I have some cells that will contain large amounts of text. Rather than display all the text by formatting as Wrap Text, is there a way to display just the first line (or even a prompt line) and access the text by clicking on a drop-down arrow?

    Thanks
    Ruth

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Hi Ruth

    Not sure of how your large text entries have been made, but this is how I've achieved a single-line version. In cell C139 (could be hidden) is the multilined entry:
    Line 1
    Line 2
    Line 3

    Each linebreak was made using Alt+Enter.

    This formula
    =IF(ISERROR(LEFT(C139,SEARCH(CHAR(10),C139))),T(C1 39),LEFT(C139,SEARCH(CHAR(10),C139)-1))
    will show the result:
    Line 1
    in its cell. The formula should deal with the case of just a single line of text, and display the result correctly.

    Another simple way is to set row height to single line. Only the first line will be displayed in the cell, but the full text will appear in the formula bar. Note the limitations though:
    Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

    I'm not sure what you're wanting to do with the dropdown though.

    Alan

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    I have a question about large amounts of text as well-I have a column in a spreadsheet (STATUS) that contains large amounts of text, and even with resizing the column width and row height, some text is still hidden. The cells are set to wrap. I want all the text to show, and I don't think I've come across this problem before. I'm woking on Excel 2000.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Displaying large amounts of text (2000 SP3)

    There is a limit of about 1000 characters to display in a cell by design


    If you have more you must use a textbox or other means.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Ah, that's it! Thanks

  6. #6
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    You could hyperlink, Gabi, to the text box (or at least, to a cell somewhere adjacent to the top lh corner of the text box. And maybe hyperlink back to where you came from.

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    The problem is that this report is to be printed and presented at our organization's House of Delegates, for over 1,000 attendees. I'm cutting and pasting into a word table. Don't have the converter installed, and the software/network guru guy is not here today to "administer" the change! <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Hi Alan

    Thanks for your reply. The Alt+Enter solution is not really a possibility as there could be several paragraphs worth of text (ie many lines). What the user was looking for via the drop-down type idea was a method of obtaining all the text. Maybe the simplest is to suggest your second idea of the single row height and educating the users of the workbook to look at the formula bar. I was looking for a more sophisticated solution but this could probably work just as well.

    Thanks
    Ruth

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    The formula I posted will display the text up to the first linebreak. This might mean showing just a single "row height" of text or several "row heights", depending on how long the first line/ paragraph is, and how the coulmn width and row height are set... lots of variables. So the same formula might display:
    This is the first line/ para.
    or
    This is the first paragraph of
    text here. It keeps going because
    there is not a linebreak until we
    reach here.


    If I understand situation properly, another solution might be to have the rows set at "single" row height, and have a mechanism (toolbar button or right-click context menu item say) to expand the height of the row containing the active cell, to show all the text. This could act as a toggle.

    Or is it possible to use the cell comment to display the "full" text? This can be done with VBA code.

    Alan

    I also just noticed this, which I didn't know, in the reference posted by Steve:
    Characters in a cell after approximately the 1,024th character are not printed and may not appear in print preview.
    Might be of significance to you if you're dealing with printed copies.

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Hi Alan

    I like your idea about having the row height at single and then toggling to autofit. It would be nice to have this as a context menu option. Have written a macro to do this but don't know how to assign this to a context menu item. Are you able to help me with this?

    Thanks very much

    Ruth

  11. #11
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Ruth,

    Attached is my "recipe" for achieving this. Right-click any row in Sheet1 and click the bottom item "Toggle Row Height". You may want to use some of your own macro code in place of mine.

    I'd appreciate some other opinions on the method by which I add and delete the right click menu item, and the ThisWorkbook events I've used. It seems to work OK for my tests, but I may be asking for some sort of trouble I haven't anticipated.

    Alan

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

    Re: Displaying large amounts of text (2000 SP3)

    Nice code, but I wouldn't rely on the Toggle Row Height item still being the last in the Cell context menu when you close the workbook. It may be unlikely that other items have been added in the meantime, but you never know...

    Public Sub AddRC_Ctl()
    DelRC_Ctl
    With CommandBars("Cell").Controls.Add(Type:=msoControlB utton)
    .Caption = "Toggle Row Height"
    .OnAction = "SetRowHeight"
    End With
    End Sub

    Public Sub DelRC_Ctl()
    On Error Resume Next
    CommandBars("Cell").Controls("Toggle Row Height").Delete
    End Sub

  13. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    If you're going for the Shortcut Menus route, you've probably chosen the safest way of doing that. It is theoretically possible, however, that it might trip over someone else's code. HTH
    Gre

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Thanks for those tips Hans. I did think of deleting the item by name rather than position, just after posting <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:
    CommandBars("Cell").Controls("Toggle Row Height").Delete

    but as always forgot to include "On Error" when trying to delete from a collection - same story with deleting before trying to Add. I like the way you've accounted for this. Question: Is it better to loop through the collection to see if an item exists, as a prelude to trying to Add or Delete, or better to use the On Error method?

    I see too that it was superfluous me fiddling with the .Count property, since items are always added to the end of a collection.

    thanks for the fine tuning.

    Alan

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying large amounts of text (2000 SP3)

    Thanks unkamunka. I remember you advocating using custom menus in preference to editing standard ones - for this reason I guess. I had thought of "nuking" the standard cell menu and replacing it with a custom copy, then restoring the original. Would this be a "safer" way to go, or is this fraught with even more danger?

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

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