'm starting to use VBA for work and I've found this VBA below, to create a list of my threaded comments on a new worksheet.
It creates a table with the number, cell, name, date, and text of the comment. And it works great, but I was hoping to find a way to include the value of the cell of the comment.
And actually it would be even better if it could be the value of the cell in the first column of the row where the comment is posted.
Hope someone can help me out with this.
It creates a table with the number, cell, name, date, and text of the comment. And it works great, but I was hoping to find a way to include the value of the cell of the comment.
And actually it would be even better if it could be the value of the cell in the first column of the row where the comment is posted.
Hope someone can help me out with this.
VBA Code:
Sub ListCommentsThreaded()
Application.ScreenUpdating = False
Dim myCmt As CommentThreaded
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Dim cmtCount As Long
Set curwks = ActiveSheet
cmtCount = curwks.CommentsThreaded.Count
If cmtCount = 0 Then
MsgBox "No threaded comments found"
Exit Sub
End If
Set newwks = Worksheets.Add
newwks.Range("A1:F1").Value = _
Array("Number", "Cell", "Author", _
"Date", "Replies", "Text")
i = 1
For Each myCmt In curwks.CommentsThreaded
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = myCmt.Parent.Address
.Cells(i, 3).Value = myCmt.Author.Name
.Cells(i, 4).Value = myCmt.Date
.Cells(i, 5).Value = myCmt.Replies.Count
.Cells(i, 6).Value = myCmt.Text
End With
Next myCmt
With newwks
.Columns(6).ColumnWidth = 50
.Columns.AutoFit
With .Cells
.EntireRow.AutoFit
.VerticalAlignment = xlTop
.WrapText = True
End With
End With
Application.ScreenUpdating = True
End Sub
Last edited by a moderator: