If you work with Excel worksheets that have a lot of cell comments in a worksheet, this tutorial could be useful. Sometimes you may insert cell comments to highlight or explain data, or your boss may insert comments in cells while reviewing your work.
If you have a huge dataset and the comments are scattered all over the worksheet, it may help to have all comments in a single place as a list.
Get a List of All the Comments in a Worksheet
In this tutorial, I will show you how to use a VBA code to get a list of all the comments in a Worksheet in a separate worksheet.
Something as shown below:
There is a VBA code in the backend that does the following things:
- It checks whether there are any comments in the active worksheet or not. If there are no comments, it quietly bows out and does nothing.
- If there are comments in the worksheet, it creates a new worksheet (“Comments”) and extract a list of all the comments in the following structure:
- Column A has the cell address that has the comment.
- Column B has the commenter name. This comes in handy if there are multiple reviewers of the same file. It will also help filter/sort based on reviewers name.
- Column C has the comment.
Download the Example File
The VBA Code
Here is the VBA code that does all the heavy lifting here.
Sub ExtractComments()
Dim ExComment As Comment
Dim i As Integer
Dim ws As Worksheet
Dim CS As Worksheet
Set CS = ActiveSheet
If ActiveSheet.Comments.Count = 0 Then Exit Sub
For Each ws In Worksheets
If ws.Name = "Comments" Then i = 1
Next ws
If i = 0 Then
Set ws = Worksheets.Add(After:=ActiveSheet)
ws.Name = "Comments"
Else: Set ws = Worksheets("Comments")
End If
For Each ExComment In CS.Comments
ws.Range("A1").Value = "Comment In"
ws.Range("B1").Value = "Comment By"
ws.Range("C1").Value = "Comment"
With ws.Range("A1:C1")
.Font.Bold = True
.Interior.Color = RGB(189, 215, 238)
.Columns.ColumnWidth = 20
End With
If ws.Range("A2") = "" Then
ws.Range("A2").Value = ExComment.Parent.Address
ws.Range("B2").Value = Left(ExComment.Text, InStr(1, ExComment.Text, ":") - 1)
ws.Range("C2").Value = Right(ExComment.Text, Len(ExComment.Text) - InStr(1, ExComment.Text, ":"))
Else
ws.Range("A1").End(xlDown).Offset(1, 0) = ExComment.Parent.Address
ws.Range("B1").End(xlDown).Offset(1, 0) = Left(ExComment.Text, InStr(1, ExComment.Text, ":") - 1)
ws.Range("C1").End(xlDown).Offset(1, 0) = Right(ExComment.Text, Len(ExComment.Text) - InStr(1, ExComment.Text, ":"))
End If
Next ExComment
End Sub How to Use this Code
There are a couple of ways you can use this code to get a list of comments from your worksheet:
#1 Copy Paste Data in the Example File
Copy paste your data (as is) in the Data tab of the download file and then run this macro.
To run this:
- Go to the Developer tab and click on Macros. It will open the Macro dialogue box.
- Select ExtractComment and click on Run. Make sure you are in the sheet that has the comments that you wish to extract.
#2 Copy Paste the Code in Your Workbook
Copy the code and paste it in the workbook from which you want to extract the comments.
To do this:
- Activate the workbook in which you are working and press Alt + F11. This will open the VB Editor window.
- In the Project Explorer on the left, right-click on any of the objects for that workbook, go to Insert –> Module.
- Paste the code in the Module code window.
- Close the VB Editor Window (or press Alt + F11 to go back to the worksheet).
Now you have the macro in your workbook. To run the macro, go to Developer Tab –> Macros. In the Macro dialogue box, select the ExtractComment macro and click in Run.
Note: Make sure you save the workbook with .XLS or .XLSM extension.
#3 Create an Add-in
If you need to use this code often, it’s best to create an add-in out of it. That way you can easily use it in any workbook (without the additional effort of copy pasting the code again and again).
Here is how to create an Add-in:
- Go to File –> Save As.
- In the Save As dialogue box, change the Save as type to .xlam.
- You’ll notice that the path of the file where it gets saved automatically changes. You can change it if you want.
- You’ll notice that the path of the file where it gets saved automatically changes. You can change it if you want.
- Open an Excel workbook and Go to Developer –> Add-ins –> Excel Add-ins.
- In the Add-ins dialogue box, browse and locate the file that you saved, and click OK.
Once an add-in has been activated, you can use it in any workbook. To do that, go to Developer –> Macros. In the Macro dialogue box, select the ExtractComment macro and run it.
Download the Example File
Hope this code will save you some time. Let me know your thoughts in the comments section.
If you work with Excel, VBA could be a powerful ally. Take your Excel Skills to the next level with the Excel VBA Course.
You May Also Like the Following Excel Tutorials:
18 thoughts on “Get a List of All the Comments in a Worksheet in Excel”
Able To Extract starts with the initial measurable data set and generates derived values
Philip
patchhere.com
Here is the corrected code and will print out comments (notes) and threaded comments (review-style comments) in excel – you’re welcome! 😉 :
Sub USEFUL_PrintCommentsAndNotes()
If MsgBox(“Create a summary of comments and notes from the current workbook?”, vbOKCancel) = vbCancel Then Exit Sub
‘ “Review” type comments
Dim AllCommentsThreaded As Excel.CommentsThreaded
Dim OneCommentThreaded As Excel.CommentThreaded
Dim AllReplies As Excel.CommentsThreaded
Dim OneReply As Excel.CommentThreaded
‘ “Yellow” notes in excel
Dim AllNotes As Excel.Comments
Dim OneNote As Excel.Comment
Dim WS, sh As Worksheet
Dim r As Range
Dim Flag As Boolean
Dim i, totalsheets As Integer
Dim LinkLocation As String
Dim Linkloc, DataLoc, ComBy, ComIn, ComText, ComType As Integer ‘column locations
‘Create a comments work sheet or append to existing comments
Flag = True ‘True if there is not already a worksheet with this name
‘Create a column location refernce for the data
Linkloc = 1
DataLoc = 2
ComBy = 3
ComText = 4
ComType = 5
For Each sh In Worksheets
If sh.Name = “Comments_Notes” Then
Flag = False
Set WS = sh ‘sets the reference worksheet to write comments to
i = WS.Range(“A1”).CurrentRegion.Rows.Count + 1 ‘ get last row
End If
Next sh
If Flag Then
Set WS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
WS.Name = “Comments_Notes”
WS.Cells(1, Linkloc).Value = “Location Link”
WS.Cells(1, DataLoc).Value = “Data at Location”
WS.Cells(1, ComBy).Value = “Comment By”
WS.Cells(1, ComText).Value = “Comment Text”
WS.Cells(1, ComType).Value = “Type”
i = 2 ‘new sheet, set comment row
‘Freeze panes for viewing
‘ActiveWindow.FreezePanes = True
End If
‘Loop through each worksheet to gather comments and notes
For Each sh In Worksheets
If sh.Name = WS.Name Then
‘MsgBox (“No Comments Here”)
‘Exit For
GoTo MoveAlong
End If
Set AllCommentsThreaded = sh.CommentsThreaded
Set AllNotes = sh.Comments
‘ loop over all threaded comments of a worksheet and get their info
For Each OneCommentThreaded In AllCommentsThreaded
With OneCommentThreaded
‘Debug.Print .Parent.Address .Author.Name, .Date, OneReply.Text
LinkLocation = “‘” & sh.Name & “‘!” & .Parent.Address ‘name of corresponding test tab”
WS.Cells(i, Linkloc).Hyperlinks.Add _
Anchor:=WS.Cells(i, 1), _
Address:=””, _
SubAddress:=LinkLocation, _
TextToDisplay:=sh.Name & .Parent.Address
WS.Cells(i, DataLoc) = “=” & LinkLocation
WS.Cells(i, ComBy) = .Author.Name
WS.Cells(i, ComText) = .Text
WS.Cells(i, ComType) = “Comment”
i = i + 1
For Each OneReply In .Replies
With OneReply
‘Debug.Print .Author.Name, .Date, OneReply.Text
‘WS.Cells(i, 1) = sh.Name & OneCommentThreaded.Parent.Address
WS.Cells(i, Linkloc).Hyperlinks.Add _
Anchor:=WS.Cells(i, 1), _
Address:=””, _
SubAddress:=LinkLocation, _
TextToDisplay:=sh.Name & OneCommentThreaded.Parent.Address
WS.Cells(i, DataLoc) = “=” & LinkLocation
WS.Cells(i, ComBy) = .Author.Name
WS.Cells(i, ComText) = .Text
WS.Cells(i, ComType) = “Comment Reply”
i = i + 1
End With
Next OneReply
End With
Next OneCommentThreaded
‘ loop over all Notes of a worksheet and get their info
For Each OneNote In AllNotes
With OneNote
‘WS.Cells(i, 1) = sh.Name & .Parent.Address
LinkLocation = “‘” & sh.Name & “‘!” & .Parent.Address ‘name of corresponding test tab”
WS.Cells(i, Linkloc).Hyperlinks.Add _
Anchor:=WS.Cells(i, Linkloc), _
Address:=””, _
SubAddress:=LinkLocation, _
TextToDisplay:=sh.Name & .Parent.Address
WS.Cells(i, DataLoc) = “=” & LinkLocation
WS.Cells(i, ComBy) = .Author
WS.Cells(i, ComText) = .Text
WS.Cells(i, ComType) = “Note”
i = i + 1
End With
Next OneNote
MoveAlong:
Next sh
this is great question I have a number of sheet how can I see all across 12 different sheet at the same time
Hello sir i want see my all comments in list so that i can easily read all the comment but ther is no more difference between cell. is there any solution to look it
I get an error in the last two rows of the code. Anyone else encounter this?
Is it possible to make it run automatically? I mean to discover when a new comment was insert and to copy it automatically to another sheet?
This macro does not catch all my comments. It stops at row 1531, but I have comments to row 1554.
Thanks for the script, it’s very useful. Could you tell me please how I may append the worksheet name to the cell address, since I have many worksheets.
Very Helpful!!
pixelfollow.com
In case there is a picture for the comment Box, how to get the same in another column
Is there a way of including the first column cell data for the comment address? This
Hey.. Have a look at this file: https://www.dropbox.com/s/anu1srirmy41ubb/Extract-Comments%20with%20comment%20for%20column.xlsm?dl=0
I have modified the code but you will need to adjust it based on your requirements.
i need vlookup vba
is it possible without VBA ?
Hello Deepak.. I don’t think this it can be done without VBA.
You might prefer using the property: ExComment.Author
Thanks for commenting Frank.. .Author property does makes sense here