Yes, it is possible to use a macro to add content to comments. For example, the following macro adds the formulae to the comments for each selected cell, or even the whole worksheet, and displays the comments in an appropriately-sized box.
Use the code given below:
- Code: Select all
Application.ScreenUpdating = False
Dim CommentRange As Range, TargetCell As Range
'skip over errors caused by trying to delete comments in cells with no comments
On Error Resume Next
'If the whole worksheet is selected, limit action to the used range.
If Selection.Address = Cells.Address Then
Set CommentRange = Range(ActiveSheet.UsedRange.Address)
Set CommentRange = Range(Selection.Address)
'If the cell contains a formula, turn it into a comment.
For Each TargetCell In CommentRange
'check whether the cell has a formula
If Left(.Formula, 1) = "=" Then
'delete any existing comment
'add a new comment
'copy the formula into the comment box
'display the comment
.Comment.Visible = True
MsgBox " To print the comments, choose" & vbCrLf & " File|Page Setup|Sheet|Comments," & vbCrLf & "then choose the required print option.", vbOKOnly
Application.ScreenUpdating = True