Excel Comment boxes resizing themselves and/or moving all over the page

Yes, all of my comments are located in a single column- Column B.

What happens is this-

1. I create a comments box in my spreadsheet  in, say, cell B75. I set it a specific size, usually three rows high by four standard columns wide. I then type notes into the box for future reference.
2. Over time the row containing the host cell may be moved up or down the spreadsheet as I add or delete other rows.
3. Later when I open the comments box to edit its contents, the editable box is no longer next to the cell or the size I started with.  It usually ends up down the sheet as far as Row 600 and/or has re-sized itself to a range of from a single thin line that barely shows up to as being as vertically long as a hundred rows!  It also will re-size itself horizontally.  In other words it is rarely the same size or in the same place.  And this goes for all the comments boxes

All I want is for the boxes to stay next to their host cells and not keep re-sizing themselves.  And no, setting the AutoShape Defaults does not work, either. Nothing I have tried does.  

What concerns me is in all of my searching through the various Excel help forums I find that many others have the same problem from as far back as 2006 with no reasonable solutions offered in all that time.  I am getting a bad feeling that this is an inherent problem with Excel that cannot be fixed.  Yet, Excel is the perfect platform for what I do save this one frustrating thing.

Simply put... is or is there not a fix?

One last thing- I am not a programmer, so if the fix is a bunch of code for me to insert I will also need to know exactly where and how to insert it into the program so as not to blow up everything.  I messed around with macros once and made a huge mess of things.

It took me a while to find it but I finally worked out how to do what MLR86 described.

1. Right click the cell with your comment and choose 'edit comment'.
2. Click on the border of the comment box (your cursor needs to look like a skinny cross with arrows on each arm), then right click and choose 'format comment'.
3. On the Alignment tab, tick 'Automatic size'.

The comment box will now automatically expand and contract to fit whatever text you enter.

On the same options window, there's a Properties tab.

This shows that the default Object positioning condition (i.e. the comment box) is 'Don't move or size with cells'.

I've changed this to 'Move and size with cells' and I'm hoping this (combined with automatic sizing) will solve the comment box positioning issue.

FOUND A SIMPLE SOLUTION !!!

Hi Guys,

This annoying error also made me crazy for several weeks. But now I finally found a solution.

A solution which doesn't need Macro, or any other type of code.

Of course the best thing would be, if Microsoft could just fix it !! But, I guess this will have to do, until then (sometime in to-thousand-and-NEVER).

Solution:

The smart thing is, you can adjust the settings of the comment box, to always fit the text.

That way, all of your text will always be shown in the comment box, no matter what!

You can do this, by choosing "Edit comment box", then right-click on the comment box,

and choose "Adjust comment" (or somehing similar. Mine is in Dansih, not english).

This will open a dialog box, where you can change a lot of things.

What you need to change, is in the "Adjust" tab, where you need to check the box saying

something like "Size automatically".

Hope you can find it. Not always easy to figure out exactly what it's called in a different language :)

One minor issue:

if you want line spacing, you need to do it manually (use the "Enter" button).

Otherwise it will just be one looong comment box.

But, I can live with that !! Hope you can too.

At least, now we know what to expect when we open the Excel-sheet, insted of it only working every tenth day :)

Hope you guys can use my "fix" too.

Take Care!

/Mike

Found 2 macros that kind of solve the problem

Macro 1:  sets the AutoSize attribute for each comment making the text fit in the box

Sub FitComments()

'Updateby20140325

Dim xComment As Comment

For Each xComment In Application.ActiveSheet.Comments

    xComment.Shape.TextFrame.AutoSize = True

Next

End Sub

Macro 2: sets the upper left corner of the comment near the upper right corner of the cell it belongs to

Sub ResetComments()

Dim cmt As Comment

For Each cmt In ActiveSheet.Comments

   cmt.Shape.Top = cmt.Parent.Top + 15

   cmt.Shape.Left = _

      cmt.Parent.Offset(0, 1).Left + 15

Next

End Sub

Both macros work on the active worksheet

A final step will be to move the comments a bit when they are overlapping each other. I did not find any macro for that

The 2 macros can be moved into each other so you only have on e amcro to use but I keep it this way so I remember what happens when I use them in a future need.