How to list certain details about all comments in a worksheet?

[leonlai]

Hi,

I have an Excel Sheet1 with data in it and several comments scattered here and there.

I think each comment has an identification number automatically assigned to it when it is created?
Maybe ID? or Item? Not sure!

I want a VBA macro to list all the comments to another sheet (say Sheet2) together with the following information:
(a) The identification of the comment.
(b) Its Text.

The reason: I will use them later.

The following thread comes close to my requirement, but it does not display the identification of the comment.
Can you, please help?

Thanks
Leon

---
https://www.mrexcel.com/forum/excel...ents.html?highlight=NEED+TO+SHOW+ALL+COMMENTS

Sub b()

Dim cmt As Comment
Dim c As Range
Dim aWs As Worksheet

Set aWs = ActiveSheet

Sheets.Add

For Each c In aWs.UsedRange
    Set cmt = c.Comment
        If Not cmt Is Nothing Then
            Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = c.Address
            Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = cmt.Text
        End If
Next c

End Sub

 

[igold]

Hi leonlai,

Welcome to the MrExcel Forum.

Does this do what you want...

Sub b()


    Dim cmt As Comment
    Dim aWs As Worksheet
    Dim nam As String
    Dim i As Long
    
    nam = ActiveSheet.Name
    Set aWs = Worksheets(nam)
    i = 1
    Sheets.Add
    For Each cmt In aWs.Comments
        Set cmt = aWs.Comments.Item(i)
            If Not cmt Is Nothing Then
                With cmt.Shape
                    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = .Name
                    Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = .DrawingObject.Text
                    i = i + 1
                End With
            End If
    Next


End Sub

 

[leonlai]

Hi, igold

Thanks a lot for your reply.

Yes, it is exactly what I need.

Best Regards,
Leon

 

[igold]

You're welcome. Thanks for the feedback.