Comment value from cell content

[Creamegg]

I would like to populate a comment with a value taken from a different
cell. Is there a simple way to reference a cell within a comment?

I am a fairly basic user with very limited knowledge of Visual Basic.

 

[Rick Rothstein \(MVP - VB\)]

You can do what you want using the worksheet's Change event. Give this a
try... right-click the tab for the worksheet with the cell whose value you
want to incorporate in the comment (this is not the cell where the comment
will be placed; rather, the cell whose value you want to retrieve), click on
View Code from the popup menu that appears and copy/paste the following in
the code window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
With Worksheets("Sheet1").Range("A1")
.ClearComments
.AddComment "Cell B1 now contains " & Target.Value
End With
End If
End Sub

For example purposes, the above code assumes the cell whose value you want
to monitor (and whose value you want to display in the comment) is B1 and
the cell where the comment will be placed is A1.

Now, whenever you change B1 on that worksheet, the comment in A1 will change
to show the new value in B1 as part of its text.

Rick

 

[Dave Peterson]

You could use a macro to do this.

But in my experience, life is simpler if I use an adjacent cell and just plop
the info into that. Then I can sort/filter or do whatever I want to those
cells.

 

[Creamegg]

Thanks for your help. It's just what I needed.

- Creamegg

 

[Dave Mills]

If you want to do this on a range of cells then set up a named range, e.g.
"CommentArea" for the cells that need to comments. Then change the use the next
two lines instead of the "If Target.Address = "$B$1" Then" to run only is the
changed cell on any one of those within the named range.

You don't need a named range but it makes it easier to read and easier to change
the range scope without needing to change the code.

Set isect = Application.Intersect(Target, Range("CommentArea"))
If Not (isect Is Nothing) Then