Snap comments to cell

Flipchick

New Member
Joined
May 24, 2021
Messages
15
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello:

I have a number of comments in my spreadsheet that have gotten dragged some distance from the cell. I know how to return them manually. But is there any kind of command that will select all of the comments in a sheet and snap them to the cell the comment references? Here you see one that has been dragged away from it's cell. TYIA
 

Attachments

  • comments in excel.jpg
    comments in excel.jpg
    25.7 KB · Views: 3

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Here is the macro snippet I use in this case. I'm using Excel 2016 so I have no way to test it on 2007 or 2010. See if you can adapt it to your project.
VBA Code:
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
            .Shape.TextFrame.AutoSize = True      'ok 2K16
            '.Shape.AutoSize = True                'use instead if version > 2K16
            .Visible = False                      '<- use if they need to stay hidden
        End With
    Next
End Sub
 
Upvote 0
I would suggest in the With...End With construct to still add
VBA Code:
.Placement = xlMove
so that in the future the processed comments will move with the cells during sorting. Be aware that this only works on comments processed with this macro, not on all comments added in the future. Unfortunately, there is no default setting for comments to move with the cells.

Artik
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,136
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top