Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    84
    Thanks
    21
    Thanked 0 Times in 0 Posts

    Angry Wandering cell comments in Excel 2007


    I'm pretty savvy about wrangling cell comments back to the desired positions by using VBA procs, but I want a software fix, not a workaround from VBA...
    I rely heavily upon cell comments.
    Despite using faithfully the Comments Format selection, "Move and Size With Cells", my comments float... drift... wander... leaving sometimes extremely long dotted tails back to the parent cells.
    Does anybody know of a non-VBA solution, so I can set aside my obsession over wandering cell comments and get on with my life?
    Thanks.

  2. #2
    New Lounger
    Join Date
    Feb 2011
    Posts
    12
    Thanks
    0
    Thanked 3 Times in 2 Posts
    Not an ideal solution, but Copy/Paste Special/Paste Special/Comments will move the comment to another cell, but you're left to delete the original comment because Paste Special doesn't work with Cut...

  3. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,356
    Thanks
    50
    Thanked 275 Times in 253 Posts
    You could use a screen tip instead.

    Right click a cell with text or a picture> hyperlink> select "Place in this document" on left> Screen Tip... upper right> Enter tip> OK> OK. Hover over the text or picture and you will see the sceen tip appear

    HTH,
    Maud
    Last edited by Maudibe; 2014-08-08 at 07:29.

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    JohnS0603 (2014-08-09)

  5. #4
    Star Lounger
    Join Date
    Jan 2002
    Location
    Hunt Valley, Maryland, USA
    Posts
    84
    Thanks
    21
    Thanked 0 Times in 0 Posts
    Thanks for the idea, Maud. However, I am dealing with (example) 332 comments in a 29 x 452-cell table... I think I might perish before I got through just this one worksheet using the screen tips method. Besides, some of the comments are 30-100 characters long!
    All the best, John

  6. #5
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,356
    Thanks
    50
    Thanked 275 Times in 253 Posts
    John,

    I have 3 more suggestions
    1. Would selecting "comments and indicators" in Options> Advanced> Display work for you? They are always displayed and they can manually be moved to the positions you want

    2. I know you are looking for a non vba solution but just consider having all your comments appear in the status bar instead. In the Options> Advanced> Display select "No comments and indicators" then add the following code to your sheet module

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Application.StatusBar = ActiveCell.Comment.Text
    End Sub
    3. You could also use the same method above to display the selected cell's comment in a reserved area of merged cells (ex. L1:M5) with
    [L1] = ActiveCell.Comment.Text

    HTH,
    Maud
    Last edited by Maudibe; 2014-08-09 at 11:36.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    RetiredGeek (2014-08-09)

  8. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,356
    Thanks
    50
    Thanked 275 Times in 253 Posts
    John,

    A non VBA alternative is to use Vlookup using the selected cell's address to find a value (the comment) in a table on a second sheet then display the value in another cell:

    =IF(ISERROR(VLOOKUP(CELL("address"),Sheet2!A1:B6,2 ,FALSE)),"",VLOOKUP(CELL("address"),Sheet2!A1:B6,2 ,FALSE))

    On the table sheet, one column would contain the cell addresses and the adjacent column would contain the comments. You would not need any commented cells.

    There is one caveat that is you must hit F9 to update the display of the comment

    HTH,
    Maud

    Vlookup1.png
    Attached Files Attached Files
    Last edited by Maudibe; 2014-08-09 at 13:53.

Tags for this Thread

Posting Permissions

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