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
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.