Fix Those Wandering Excel Comments

image 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.

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.

CommentSize01

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.

____________

You may also like...

12 Responses

  1. Stuart Valentine says:

    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. Robert Richard says:

    You just saved me 15-20 minutes

    Thank you so much!

  3. Bru Allen says:

    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!

  4. Jonathan says:

    Thank you!!!

    I was completely new to macros with a huge spreadsheet full of comments.

    You saved me 2 days of my life

    THANK YOU!!!

    P.S I worked out what to do with your code here http://www.youtube.com/watch?v=8pfdm7xs3QE

  5. AlixK says:

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

  6. Gordon says:

    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!

  7. Navneet Rajwar says:

    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

  8. Divya says:

    wow… it really works….a bigggggggggggggg thank u :)

  9. RileyDog says:

    Why isn’t this built in to Excel? Who wants a comment they can’t read? Thank you so much!

  10. Sweets says:

    Woot! Thanks for the Marcos.

  11. AJ says:

    THANK YOU SO MUCH. WHOEVER MADE THIS AVAILABLE IS THE BEST!

  12. DP says:

    Yes, this just saved me HOURS! Thank you sooooooo much!!!!!

Leave a Reply to Bru Allen Cancel reply

Your email address will not be published. Required fields are marked *