Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Copy Comments (2000)

    I have a title with a comment in a cell. I want to show that cell in two locations.

    If I copy the cell, I can copy the comment. However, if I change the comment in one location, I have to remember to recopy the original cell.

    Alternatively, if I use a formula to reference the original cell it does not copy the comment.

    How do I show the title and comment in the 2nd location, and have it change when I edit the first location?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Copy Comments (2000)

    Dave

    How about writing some VBA. I do not think you can do much with Comments, so the VBA would be the way. Something like:

    Range("A1").Copy
    Range("C1").PasteSpecial Paste:=xlPasteComments

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Re: Copy Comments (2000)

    That doesn't seem to solve the problem. In some sense, I guess I want to put a field in one cell that looks at a source cell. When the source changes, the field referencing it changes as well.

    The VBA suggestion worked fine for copying and pasting - but it didn't update automatically when the source cell was updated.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Comments (2000)

    You can use the sheetchange event of thisworkbook to update your comments. The problem is that changing the comment does not fire this event. But, the code below updates the comments as soon as a cell is changed in worksheet. For large worksheets, this might be a problem because for each cell the code runs through the UsedRange to see if there are links to this cell. This might take some time if your worksheet is quite large.

    Just a remark that I want to add: you have to use absolute adresses for your links, like =*$A$1" or the code will not work.

    <pre>Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cell As Range
    Dim cellbis As Range
    Application.EnableEvents = False
    For Each cell In ActiveSheet.UsedRange
    For Each cellbis In ActiveSheet.UsedRange
    If cell.Formula = "=" & cellbis.Address Then
    cellbis.Copy
    cell.PasteSpecial Paste:=xlPasteComments
    End If
    Next
    Next
    Application.CutCopyMode = False
    Application.EnableEvents = True
    End Sub
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •