Reference cell to comment of cell

[Celia9]

Hi dear all,

I have a long text in cells in a table (see file, column R). I rather have the text from the cell R5 in the comment/note of cell P5.

Is this possible? And if yes, how do I do it?

[#2 · Accepted solution]

@Celia9 

Try this macro:

Sub MoveTextToComments()
    Dim w As Worksheet
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set w = ActiveSheet
    ' Find last used row
    m = w.Range("R" & w.Rows.Count).End(xlUp).Row
    ' Remove any existing comments from column P
    w.Range("P5:P" & m).ClearComments
    ' Loop through the rows
    For r = 5 To m
        ' Add comment to cell in column P
        w.Range("P" & r).AddComment Text:=w.Range("R" & r).Text
    Next r
    ' Optional: clear column R
    'w.Range("R5:R" & m).ClearContents
    Application.ScreenUpdating = True
End Sub

[#3]

Thanks Hans! It works like a charm!

Can I also add a line to auto-adjust the size of the note so I can see all the text? Now I only see 3 lines.

[#4]

@Celia9 

The result might no be what you wanted: change

        w.Range("P" & r).AddComment Text:=w.Range("R" & r).Text

to

        w.Range("P" & r).AddComment(Text:=w.Range("R" & r).Text).Shape.TextFrame.AutoSize = True

[#5]

Sorry, I was unclear. I do want a maximum of 320 pixels wide, but wrap text and make it as long as necessary.

[#6]

@Celia9 

I don't think that would be easy - you'd have to break up the text from the cell into separate lines. Sorry!

[#7]

Thanks anyway Hans!