How to get cell address from comment shape object in Excel VBA?

user_9034269 0
Viewed 79

I'm iterating through shapes of excel sheet. Some of shapes are comment shapes (sh.type == msoComment). I need to find cell attached to comment shape.

for each sh in activesheet.shapes
  if sh.type == msoComment then
    debug.print get_cell_from_shape(sh).address
  end if
next

I know that I can iterate through cells and check if there is a comment, but it's to slow. I'm working on huge Excel files with only few comments. I've tried sh.parent but it returns worksheet. I've tried sh.topleftcell, but its wrong cell (not the one I need).

vba excel
2 Answers

Never mind... I can iterate through comments directly

for each com in activesheet.comments
  debug.print com.parent.address
next

and now .parent works fine

user_90342690

You may want to consider iterating through both the ActiveSheet.Comments and ActiveSheet.CommentsThreaded because what used to be called a Comment is now referred to as a Note in Excel 365.

Cell Menu Clip

  • New Comment will add a CommentThreaded to the cell
  • New Note will add a Comment to the cell.
Function GetCommentedCells() As Collection
    Dim Collection As New Collection
    Dim Comment As Comment
    For Each Comment In ActiveSheet.Comments
        Collection.Add Comment.Parent
    Next
    
    Dim CommentThreaded As CommentThreaded
    For Each CommentThreaded In ActiveSheet.CommentsThreaded
        Collection.Add CommentThreaded.Parent
    Next
    
    Set GetCommentedCells = Collection
End Function
user_99127140
Related