Re: Comment value from cell content

Giganews Newsgroups
Subject: Re: Comment value from cell content
Posted by:  Dave Mills (
Date: Fri, 18 Jul 2008

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

On Thu, 17 Jul 2008 13:23:41 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SP…> wrote:

>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.
>"Creamegg" <colinbainbridg…> wrote in message
>>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.

Dave Mills
There are 10 type of people, those that understand binary and those that don't.



In response to

Re: Comment value from cell content posted by Rick Rothstein \(MVP - VB\) on Thu, 17 Jul 2008