Adjusting comment positions &

[Tibe2010]

I have a spreadsheet with comments in several cells. I uploaded the spreadsheet to Google drive to share with others. I exported the spreadsheet back to Excel in my local drive. Now the comments are all over the place. Some of them are squished and look like a line when hovering over the cell. They are also moved away from the cell. Do you know of VBA code that can search in the entire workbook for cells with comments then move the near the cell and adjust the window where the comments are displayed properly?

Thank you in advance.

 

[rollis13]

Have a try with this snippet:

Sub ResetComments()
    'resets comments to Original Position
    Dim cmt   As Comment
    For Each cmt In ActiveSheet.Comments
        With cmt
            .Shape.Top = cmt.Parent.Top + 5       '<- adjust distance offset if needed
            .Shape.Left = cmt.Parent.Offset(0, 1).Left + 5 '<- adjust distance offset if needed
            .Visible = False                      '<- use if they need to stay hidden
        End With
    Next
End Sub

 

[Tibe2010]

Thank you for the code you shared. It moved the comment box closer to the cell as expected but the comment box is still squished as you see in the screenshot attached.

 

[rollis13]

As fare as I know the original sizes of the comment boxes are no longer available, so try adding this other parameter:

.Shape.TextFrame.AutoSize = True

 

[Tibe2010]

Thank you for the additional code. Unfortunately, I got a Run-time error '1004': Application-defined or object-defined error". First I added this code at the beginning of the "With cmt", run the script then moved it to the end just in case it makes a difference but it did not make a difference.

 

[rollis13]

Not sure since I don't have any experience with the Mac versione of Excel, so maybe, your 2019 MacOS doesn't accept this other parameter. Works fine with Windows version.

 

[Tibe2010]

Ok, Thanks for trying anyway.

 

[rollis13]

Last go after a quick search in web, try this since it is common for 365 version (not for my 2016 version):

.Shape.AutoSize = True