Fix Those Wandering Excel Comments

Do you ever open an Excel workbook, and find that tragedy has struck your comments? You spent hours inserting those comments, and making them just the right size and shape. Then, for no apparent reason, everything changes. Comments are in the wrong place, and wrong size. Here’s how to fix those wandering Excel comments.


Wandering Excel Comments

This screen shot shows an example of wandering Excel comments, and incorrect comment sizes.

CommentSize01

  • Some comments have flattened to a thin line.
  • Other comments are so small that you can barely read the first word.
  • A few comments have wandered far from their cell, and the connecting line stretches across the entire window.

What a mess! Fortunately, you can quickly get things back in place, by using an Excel macro or two. Use the sample macros below, to fix wandering Excel comments, and to make them the correct size again.

Put Comments Back in Place

If your comments have slithered across the spreadsheet, you can use this macro to put them back in their parent cell.

Sub ResetComments()
Dim cmt As Comment
For Each cmt In ActiveSheet.Comments
   cmt.Shape.Top = cmt.Parent.Top + 5
   cmt.Shape.Left = _
      cmt.Parent.Offset(0, 1).Left + 5
Next
End Sub

Get Comments Back in Shape

For comments that have shrunken to thin slivers, you can use this macro to get them back to a normal size.

Sub Comments_AutoSize()
'posted by Dana DeLouis  2000-09-16
Dim MyComments As Comment
Dim lArea As Long
For Each MyComments In ActiveSheet.Comments
  With MyComments
    .Shape.TextFrame.AutoSize = True
    If .Shape.Width > 300 Then
      lArea = .Shape.Width * .Shape.Height
      .Shape.Width = 200
      ' An adjustment factor of 1.1 seems to work ok.
      .Shape.Height = (lArea / 200) * 1.1
    End If
  End With
Next ' comment
End Sub

More Excel Comment Macros

For more Excel comment macros, please visit the Excel Comment VBA page on the Contextures website.
____________

42 thoughts on “Fix Those Wandering Excel Comments”

  1. I use a variation on this that I wrote myself, differences being:

    1) I set the width/height based on parsing the comment text into numbers of characters and numbers of carriage returns to estimate it that way
    2) I insert at the top of the comment a date stamp, which gives a record of when it was raised, and hence how old it is.

  2. Amazing, this has saved me hours. I have a macro that can insert thousands of comments and some some reason a portion of these decided to change size and move hundreds of rows away. This has cleaned it all up!

  3. Had to resort to Google as IT could not fix this issue at work. Thank you so much for saving my spreadsheet!

  4. Don’t know the first thing about macros and can’t even spell VBA, but being able to cut-and-paste your code fixed a long-standing problem for me. I’m very grateful. Y’all done good!

  5. Hi,
    I have used this above macro & it really works but I wanted to know a code which can be added so that it can be used in a selected range when filter is applied.
    Thanks
    Navneet

    1. That’s a good question.

      Inside of a separate module it could be placed in the sheet_activation or before_close event so you never have to think about it again.

  6. I tried to install the macros but, when I click alt-F11 nothing happens. This is all new to me. Never installed a macros before. I just want my comments in excel sheet to quit wandering. lol

  7. Just want to leave my thanks as well. And to whomever left the 5-minute You Tube tutorial. My comments never looked so nice and uniform.

  8. Wonderful, thanks alot! We had a colleague who had tonnes of comments on a single excel document with frozen panes and hidden columns/rows and jumping and hidden comments all over the place.

    This helped to save her the time to re-order all of it again!

  9. For years I’ve put up with “disconnects” between cells and the associated comments. Just used your Reset Comments macro – fantastic!! Many thanks.

  10. Thank you so much for the helpful macro! You just saved me the headache of manually fixing a bunch of comments in a large spreadsheet.

  11. Thank you so much for the script. I had several notes that suddenly change size and position and I was about to waste hours fixing it until I found your post! Thank you so much!

  12. Just joining the fan club here – comments have been a PITA since last century, and I’d hoped the upgrade to interactive notes would have helped, but they didn’t fix it. Fortunately, you did 🙂

  13. Thank you so much!

    I think the mess has to do with deleting/inserting/hiding/unhiding rows or columns. It’s like Excel stores absolute sheet coordinates for comments instead of coordinates relative to their parent cells. Even height and width seem to depend on absolute coordinates.

    I know what problem i causes, but what problem was it implemented to solve?

  14. Contextures is by far the best help I have seen in all my years of struggling with solutions to the many unpleasant surprises Excel throws to us.

    Your solution to this comments issue is awesome!!!

    Thank you so much, you saved me hours..

  15. Works great! Thanks, but I’m curious as to why it happens at all? What is the shortcoming in Excel that causes this?

  16. To those asking “why it happens” and “can it be avoided?” – Yes. Manually select the comment cell … Edit Comment … select the comment border … rightclick Format Comment … tab Properties … choose Move but don’t size with cells … OK n times. Comment will no longer get messed up by add/delete rows or columns. To fix all comments on active sheet…

    Sub FixComments()
    For Each s In ActiveSheet.Shapes
    If s.Type = 4 Then ‘a comment
    With s
    .Placement = xlMove
    End With
    End If
    Next
    End Sub

  17. Thank you so so much, Debra!
    I really couldn’t face the frustration of moving all my comments, so this was an amazing find.

  18. Hi there,

    I don’t really know how to thank you enough, but you really helped me a lot to solve this issue. It works like a charm.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.