Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of comments text for compressing text (Excel)

    See also <post:=619,758>post 619,758</post:>
    The client is plagued with users who maintain that Excel makes an excellent word-processing tool. With a few calculations in.
    My client's challenge is to debug the calcs in the forest of text.
    Selecting the entite sheet and autofit the column widths results in the text labels overpowering the calculations.
    The attached workbook has two macros: SheetCommentIn and SheetCommentOut.
    To see it in action:<pre>Select the entire sheet and double-click between two column margins.
    Column A in particular will be much wider than the remaining columns.
    Run the macro SheetCommentOut.
    Observe that text cells longer than 10 characters are truncated with the original text being sent to comments.
    Select the entire sheet and double-click between two column margins.
    Column A in particular will be about as wide as the remaining columns.
    Debug the now-visible calculations.
    Run the macro SheetCommentIn and observe that the original text content is re-displayed.</pre>


    An interesting side-note from my explorartion of .Comments:
    Cell notes have been replaced by range comments. I could find very few methods/properties that support Comments, but a few useful ones that still support Notestext.
    See the utility function HasComment for an example.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Use of comments text for compressing text (Excel)

    Isn't it easier to use Edit-Goto-Special... and select Formulas? You can then see (or highlight) all the formula cells on a sheet easily.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of comments text for compressing text (Exc

    > You can then see (or highlight) all the formula cells on a sheet easily.
    This is true, and it works when that's what you want.
    In this case the need was to see the sheet ("eyeball it") in general without the long text strings ('word processing stuff") getting in the way.
    They wanted to see the first few (ten was nominated) characters of any text label, and make use of Auto Column Width without the long strings dominating the fitting.

Posting Permissions

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