How To Get All Comments In A Workbook With VBA

Comments can be a great way to add a small bit of documentation to your spreadsheets. If your spreadsheet gets too big and with many sheets then important comments can easily get lost somewhere in the workbook and forgotten about. Here we’ll show you an easy way to get all the comments in a workbook. We will also add a hyperlink to the original location of the comment for ease of tractability.


First create a new sheet called “Comments” for the purpose of writing out all the comments. Then add this bit of code into the visual basic editor.


Sub GetAllComments()

Dim WS As Worksheet
Dim Cmnt As Comment
Dim Count As Long

Count = 0

For Each WS In ActiveWorkbook.Worksheets
    For Each Cmnt In WS.Comments
        Worksheets("Comments").Range("A1").Offset(Count, 0).Parent.Hyperlinks.Add _
            Anchor:=Worksheets("Comments").Range("A1").Offset(Count, 0), _
            Address:="", _
            SubAddress:="'" & WS.Name & "'!" & Cmnt.Parent.Address, _
            TextToDisplay:="'" & WS.Name & "'!" & Cmnt.Parent.Address
        Worksheets("Comments").Range("B1").Offset(Count, 0).Value = Cmnt.Author
        Worksheets("Comments").Range("C1").Offset(Count, 0).Value = Cmnt.Text
        Count = Count + 1
    Next Cmnt
Next WS

End Sub




Now all you have to do is run the code and all your comments will appear in the sheet you created called “Comments” and you will be able to click the hyperlink to go to the cell containing the comment.


About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.


Related Posts



Get the Latest Microsoft Excel Tips

Follow Us

Follow us on social media to stay up to date with the latest in Microsoft Excel!