list sheet comments

[canvas]

Hi,

I have a sheet with a lot of comments. I would like to create a macro
that lists all the comments of the sheet in column F. I´ve tried
something similar like this:

For Each cmt in ActiveSheet.Comments
MsgBox cmt.Text
Next cmt

But I don´t know how to list each comment in each cell of column F
instead of a msgbox.

thanks

 

[Dave Peterson]

Dim StartCell as Range
dim Cmt as comment

with activesheet
set startcell = .range("F1")
for each cmt in .comments
startcell.value = cmt.txt
'come down to the next cell
set startcell = startcell.offset(1,0)
next cmt
end with

(untested, uncompiled)

 

[Dave Peterson]

typo:
startcell.value = cmt.txt
should be:
startcell.value = cmt.text '.TEXT was spelled incorrectly.

 

[Dave Peterson]

In fact, depending on what the comment contains, it may be better as:

Dim StartCell as Range
dim Cmt as comment

with activesheet
set startcell = .range("F1")
for each cmt in .comments
with startcell
.numberformat = "@" 'text
.value = cmt.tExt
end with
'come down to the next cell
set startcell = startcell.offset(1,0)
next cmt
end with

 

[canvas]

Thanks, dave. That worked great!