vbaexcel

Is there a way to retrieve the cell address of a comment in Excel?


I am writing a short macro to manipulate some comments in Excel. It would be useful to have a way to find out the cell address/location of a comment in my active worksheet - is there any way to do this with the comment object in Excel? Or failing that, any clever workaround that would give me the same result?

Some illustrative pseudo-code of what I'm trying to achieve:

dim wb as Workbook
dim ws as worksheet
dim cmt as Comment

set wb = ActiveWorkbook

for each ws in wb.sheets
    for each cmt in ws.comments
        debug.print cmt.address ' Pseudo code
    next cmt
next ws

Solution

  • Try:

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cmt As Comment
    
    Set wb = ActiveWorkbook
    
    For Each ws In wb.Sheets
        For Each cmt In ws.Comments
            'Debug.Print cmt.Address ' Pseudo code
            Debug.Print cmt.Parent.Address
        Next cmt
    Next ws
    

    For info: .parent returns the parent object of the comment object, which in this case would be the cell.