Forum Discussion
Copying the value of Comment into a cell
I have a large spreadsheet that I have to change to make it much more "user friendly" for folks who are not so familiar with Excel. This sheet has over 100 "comments" that I need to translate into regular cells so each can be viewed without hovering over a comment mark. Is there a way to copy the text in a "comment" so that I can paste it into a cell? Or, at least, is there a way to get a comment to stay visible so that I can type it into a cell myself? TIA for the help.
5 Replies
kwilkies This VBA code will extract the comments into the cells containing the comment. It will replace any existing contents of the cells with the comment text, so be careful / save a back-up copy of your file before trying it.
Example file with steps to follow is attached.
Sub ExtractComments() If Not TypeName(Selection) = "Range" Then Exit Sub Dim rng As Range Set rng = ActiveWindow.RangeSelection On Error Resume Next For Each c In rng.Cells c.Value = c.Comment.Text Next End Sub- Very nice!
A note for folks using Microsoft 365: if the above isn't working, then try replacing "Comment" with "CommentThreaded".
- Never mind. I think I FINALLY found a solution.
"Never mind. I think I FINALLY found a solution."
Fantastic. Thanks for sharing your solution so others may benefit. Learn life.
Sub commennt() Dim i As Integer For i = 1 To 10 Tabelle1.Cells(i, 2).Value = Tabelle1.Cells(i, 1).Comment.Text Next i End SubMaybe with this code if your comments are in cells A1:A10.
