By using the Comments
property of a worksheet it is possible to retrieve the number of comments in said worksheet by accessing its Count
property in turn: Worksheets(1).Comments.Count
.
The Comment
object is a member of the Comments collection containing the following properties: Application
, Author
, Creator
, Parent
, Shape
and Visible
.
Given this information I was only able to retrieve the number of comments per worksheet but would also like to see the amount and/or ratio of resolved to open comments. Am I missing some hack to utilize the provided properties (maybe like Visible
) to somehow get information of whether the comment was marked as resolved?
It is not possible to delete the resolved comments since they are used as some sort of documentation of the decisions and changes.
threaded comment
has the resolved
status. Normal comment (Insert by Review > Notes > New Note) doesn't have the status.In M365, the terms comments
and notes
can be a bit confusing.
Comments
group actually works with threaded comments
object.Notes
drop-down menu works with comments
object.Microsoft documentation:
Sub CountCmt()
Dim ct As CommentThreaded, iCnt As Long, iTotal As Long
iTotal = ActiveSheet.CommentsThreaded.Count
If iTotal = 0 Then
MsgBox "No comments"
Else
For Each ct In ActiveSheet.CommentsThreaded
If ct.Resolved Then iCnt = iCnt + 1
Next
MsgBox "Resolved comments: " & iCnt & vbCr & _
"Total comments: " & iTotal
End If
End Sub