Comments don't stay where they belong

[kajero52]

I have no idea how to write complicated macros so I try to find something on the Internet I can use. So far I haven't found much. Maybe some of you gurus could help me? Thanks for any assistance you can give me.

I have a spreadsheet that has 945 rows. It lists all the books since 2013 whether or not I have read them. Most of the rows have comments. When I try to view the comments sometimes they are way far away from the cell they are attached to. See example below.

I did find a macro o n YouTube that keeps the macro with the cell but then I can't read the comments easily
Sub ResetComments()
'Excel 10 Tutorial
Dim pComment As Comment
For Each pComment In Application.ActiveSheet.Comments
pComment.Shape.Top = pComment.Parent.Top + 5
pComment.Shape.Left = pComment.Parent.Offset(0, 1).Left + 5
Next
End Sub

This sometimes happens. Is there a way to make all the comments views the same size for easier reading. It really isn't necessary to have all the comment displayed at once but when the comment cross so many columns it is hard to read.

 

[Jaafar Tribak]

Is there a way to make all the comments views the same size for easier reading. It really isn't necessary to have all the comment displayed at once but when the comment cross so many columns it is hard to read.

You can reduce the width of the comments and increase their height manually or via code (similar to the code you have above)

 

[Peter_SSs]

Along the lines suggested by @Jaafar Tribak you could give this adaptation a try. It tries to auto-fit each comment, while setting a maximum value for the width of the comment box
Experiment with changing the 'Const' line value and see if you can find something that suits your needs.

Sub ResetComments_v2()
  Dim pComment As Comment
  Dim CmtArea As Long
 
  Const MaxWidth As Long = 300  '<- Try changing this number
 
  For Each pComment In Application.ActiveSheet.Comments
    With pComment.Shape
      .Top = pComment.Parent.Top + 5
      .Left = pComment.Parent.Offset(0, 1).Left + 5
      .TextFrame.AutoSize = True
      If .Width > MaxWidth Then
        CmtArea = .Width * .Height
        .Width = MaxWidth
        .Height = CmtArea / MaxWidth * 1.1
      End If
    End With
  Next pComment
End Sub

 

[kajero52]

I can't remember what I did but I fixed it

 

[Artik]

Well, yes, after more than two years you can forget.
I want to point out that the default positioning setting for inserted comments is “Do not move or resize with cells”. This setting causes cells to change position when sorting cells containing comments, while comments remain in their original place. You would need to create a loop through all the comments and change the Placement property from xlFreeFloating to xlMove. Unfortunately, new comments still have the default setting of xlFreeFloating and it seems to me that this cannot be changed.

Artik

 

[Peter_SSs]

FYI, the 'Mark as solution' option is to help future readers quickly find the solution if they have a similar question. As such it should only mark an actual solution post. Since post #4 does not contain a solution to your original question I have removed the 'Mark as solution' tick from that post.

 

[kajero52]

FYI, the 'Mark as solution' option is to help future readers quickly find the solution if they have a similar question. As such it should only mark an actual solution post. Since post #4 does not contain a solution to your original question I have removed the 'Mark as solution' tick from that post.

Can you explain how to mark as solution? I think I am not following how to do it.
Thanks!!!!

 

[Peter_SSs]

Can you explain how to mark as solution? I think I am not following how to do it.
Thanks!!!!

There is a link about it right at the bottom of my signature block below. Follow that link to learn more.
You had marked post #4 as the solution, but that is clearly not the case and that was what my previous post was about.