How To Get All Comments In A Workbook With VBA

2016-12-11

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

 

Step-001-How-To-Get-All-Comments-In-A-Workbook-With-VBA-1024x630 How To Get All Comments In A Workbook With VBA

 

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.

Subscribe

Advertisement

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get The Latest News

Follow Us

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

Pin It on Pinterest

Share This