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.
- 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.
____________
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.
You just saved me 15-20 minutes
Thank you so much!
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!
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
Thank you for this link to what the heck to do with the macro! So helpful.
This whole discussion is saving my sanity..
Had to resort to Google as IT could not fix this issue at work. Thank you so much for saving my spreadsheet!
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!
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
wow… it really works….a bigggggggggggggg thank u 🙂
Why isn’t this built in to Excel? Who wants a comment they can’t read? Thank you so much!
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.
Woot! Thanks for the Marcos.
THANK YOU SO MUCH. WHOEVER MADE THIS AVAILABLE IS THE BEST!
Yes, this just saved me HOURS! Thank you sooooooo much!!!!!
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
Thank you so much for this – saved me ages!
Thanks. Worked wonderfully
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.
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!
For years I’ve put up with “disconnects” between cells and the associated comments. Just used your Reset Comments macro – fantastic!! Many thanks.
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.
Really helpful….and it works!!!
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!
You’re welcome, Natalie! Thanks for letting me know that it helped you.
Works like a charm! Thank you very much you good lady/man!
awesome, saved me hours of work, nice one…
Thank you so very much! This works so well
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 🙂
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?
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..
Thank you, Felipe!
Many many thanks!
Works great! Thanks, but I’m curious as to why it happens at all? What is the shortcoming in Excel that causes this?
I have this problem but with notes. Any tips?
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
I did and yes it sorted out. Thanks buddy
Thank You!!!
Thank you so much! This really helped me a lot. Saved myself a few hours!
Thank you so so much, Debra!
I really couldn’t face the frustration of moving all my comments, so this was an amazing find.
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.
thanks so much, it worked and this is amazing
awesome, easy fix to a pesky problem. THANK YOU!