Select the cell holding text (large circle), select the cell to receive the comment (small circle), run the macro.
I wrote this to semi-automate a cleanup process I'm running on a set of workbooks, where the workbook designers didn't know about Comments, and used cells way-over to the far right to annotate their work.
Code: Select all
Sub LoadCellAsComment()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' Function: LoadCellAsComment
'''
''' Comments: Load the text of one cell as the comment of a second cell.
'''
''' Arguments: none
'''
''' Returns: none
'''
''' Comments:
'''
''' Date Developer Action
''' --------------------------------------------------------------------------
''' 2010/05/02 Chris Greaves Created
'''
If Selection.Areas.Count = 2 Then
Dim rngSource As Range
Set rngSource = Selection.Areas(1).Cells(1) ' use the first cell of the selected area.
Dim rngTarget As Range
Set rngTarget = Selection.Areas(2).Cells(1) ' use the first cell of the selected area.
'''
Dim strComment As String
strComment = rngSource.Value
rngSource.Delete ' clear the text once we have it in hand.
'''
With rngTarget
.ClearComments ' in case any present from a previous (failed) attempt
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Application.UserName & Chr(10) & strComment
End With
Else
MsgBox "Select a cell with text then Ctrl-Select a cell to receive that text as comment"
End If
End Sub