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

 

step-001-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 qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Related Posts

Comments

0 Comments

Get the Latest Microsoft Excel Tips

Follow Us

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