How can I retrive the contents of a comment box in microsoft excel

R

Rg

I have inserted comments, and now i want to get the contents of each comment
in a cell. Is it possible?
 
N

Nick Hodge

Rg

The code below will take the comments from the active sheet and put them in
column A (Address) and column B (Text) on a new sheet inserted into the book

Sub ExtractCommentsToNewSheet()
Dim wksActive As Worksheet
Dim wksNew As Worksheet
Dim lCnt As Long
Dim rngComm As Range, myCell As Range
Set wksActive = ActiveSheet
Set wksNew = Worksheets.Add
Set rngComm = wksActive.Cells.SpecialCells(xlCellTypeComments)
lCnt = 1
For Each myCell In rngComm
With wksNew.Range("A" & lCnt)
.Value = myCell.Address
.Offset(0, 1).Value = myCell.Comment.Text
End With
lCnt = lCnt + 1
Next myCell
wksNew.Columns.AutoFit
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top