m2oTech.com

  • Increase font size
  • Default font size
  • Decrease font size

A macro to un-mess Excel comments

Print PDF

Ever experienced having tens of comments in Excel and finding they are all over the place (e.g. because you resized/added/removed cells)? I was quite sure there must be a better way and someone already did a macro to "fix" this... so here it is, I found this originally posted at this blog and allowed myself to copy and paste it (if you are the author and you want credits please let me know!).

All you need to do is:

  1. Add a macro to your spreadsheet (not "record a macro")
  2. In the VBA editor that opens paste the following code
  3. Optionally create a shortcut to the macro so that you can invoke it easily (else can run it from menu when needed)

This macro will place all of your comments back to where they belong (next to the cell they refer to).

Note: If you are trying to find a way to move comments on the sheet where you would like them to be... that's a different story. It does not seem to be possible!

Sub CommentFix()

' This macro modifies all comments in all open workbooks to:
' (1) move and size with cells
' (2) be physically positioned near the cell to which they correspond
' (3) be optimally sized appropriate to the text within
' This macro was created by combining code posted on the following website:
' http://www.contextures.com/xlcomments03.html

Dim thisfile As Workbook
Set thisfile = ActiveWorkbook
Dim MyWorkbook As Workbook
Dim MySheet As Worksheet
Dim MyComment As Comment
Dim CommentCount As Long
Dim lArea As Long
Dim fixed As Boolean
fixed = False
On Error GoTo NeedToUnprotect
For Each MyWorkbook In Workbooks
MyWorkbook.Activate
For Each MySheet In MyWorkbook.Sheets
MySheet.Activate
CommentCount = 0
For Each MyComment In MySheet.Comments
With MyComment.Shape
.Placement = xlMoveAndSize
.Top = MyComment.Parent.Top + 5
.Left = MyComment.Parent.Offset(0, 1).Left + 5
.TextFrame.Characters.Font.Name = "Tahoma"
.TextFrame.Characters.Font.Size = 8
.TextFrame.AutoSize = True
CommentCount = CommentCount + 1
End With
If MyComment.Shape.Width > 300 Then
lArea = MyComment.Shape.Width * MyComment.Shape.Height
MyComment.Shape.Width = 200
MyComment.Shape.Height = (lArea / 200) * 1.1
End If
Next MyComment
If CommentCount > 0 Then
MsgBox ("A total of " & CommentCount & " comments in worksheet '" & MySheet.Name & "' of workbook '" & MyWorkbook.Name & "'" & Chr(13) & "were repositioned and resized.")
fixed = True
End If
Next MySheet
Next MyWorkbook
thisfile.Activate
If fixed = False Then
MsgBox ("No comments were detected.")
End If
On Error GoTo 0
Exit Sub

NeedToUnprotect:
MsgBox ("You must unprotect all worksheets before running the macro.")
thisfile.Activate
Exit Sub

End Sub

 

 

Add your comment

Your name:
Subject:
Comment:
  The word for verification. Lowercase letters only with no spaces.
Word verification: