Copy Cell Comments Automatically To Another Workbook



  • What i need help to do is the following: How do I take the changes made from the first work book and insert them in a cell on the second work book. Where in the code above do I Put this command. Can someone please help me with this?

  • Re: Inserting Changes Code


    Only use this code in the Sheets Module You want the code to work in


    jiuk
    [vba]
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Rem reWritten by: Jack in the UK for http://www.OzGrid.com
    Rem Our WEB site http://www.Excel-It.com


    Dim preValue As String
    Dim i As Long
    i = 1
    If Target.Count > i Then Exit Sub
    If Intersect(Target, Range("$A$1:$M$42")) Is Nothing Then Exit Sub
    ''' jiuk Note this added line
    preValue = Target.Value
    Target.ClearComments
    Target.AddComment.Text Text:="Previous Value was " _
    & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " _
    & Environ("UserName")
    theEnd:
    Rem - jiuk - do something -- or -- NOT
    Exit Sub
    End Sub
    [/vba]

  • Re: Inserting Changes Code


    Version two - thy this version


    jiuk
    [vba]
    Private JR_myRange As Excel.Range
    Private preValue As String
    Private Const i As Long = "1"

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Rem reWritten by: Jack in the UK for http://www.OzGrid.com
    Rem Our WEB site http://www.Excel-It.com


    Set JR_myRange = Range("$A$1:$M$42")
    If Target.Count > i Then Exit Sub
    If Intersect(Target, JR_myRange) Is Nothing Then Exit Sub
    ''' jiuk Note this added line
    Rem - jiuk - stops input to empty cell
    If IsEmpty(Target.Value) = True Then Exit Sub
    preValue = Target.Value
    With Target
    .ClearComments
    .AddComment.Text Text:="Previous Value was " _
    & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " _
    & Environ("UserName")
    End With
    theEnd:
    Rem - jiuk - do something -- or -- NOT
    Target.Calculate
    set JR_myRange = Nothing
    Exit Sub
    End Sub
    [/vba]

  • Re: Inserting Changes Code


    Im looking at the code trying to understand it. Sorry im coding stupid. So lease bear with me. For the very first code. What does it do??? So will i put that code on the second work book and it will take the changes and insert it into the second book. Thank you ahead of time for your patiences.

  • Re: Inserting Changes Code


    There is no mention of a second workbook in your code. That code reads the cell's value and if the cell changes it records the previous value in a cell comment. What exactly are you wanting to do?

  • Re: Inserting Changes Code


    Well, What i did was this in the first code. I just made it monitor any changes and show the changes on that same workbook. I now want those changes to be placed in a cell on the second workbook also.

  • Re: Inserting Changes Code


    Hang on. Have You tested my second code? Does it work OK for Your original question? All i have done is reVamp Your code to do as You asked ie track changes in the Comment Box


    If this works i guess then yes i could (i hope) Off set this preserved value to a holding WorkBook for changes at the same WorkSheets and cells address - i assume as some kind of Log Record or Audit Trace


    You have changed the original question here, with out testing the codes. You ask what it does? It answers Your question and is just based on Your original code


    jiuk

  • Re: Inserting Changes Code


    Do you have a workbook already open/ Probably the easiest way would be to simply copy the cells & pastespecial Comments to the other workbook, maybe using code similar to this

  • Re: Inserting Changes Code


    How about

    But please, in future, take more care with your thread titles. One like Copy Cell Comments Automatically To Another Workbook would have been MUCH better.

  • Re: Copy Cell Comments Automatically To Another Workbook


    Code
    1. Worksbooks ("Book2.xls").Sheets(1).cell(.Row, .Column).PasteSpecial Paste:=xlPasteComments




    I get an error when on this line of the code. Do I change Workbooks name???

  • Copy Comment into another worksheet cell


    Code
    1. Target.AddComment.Text Text:="Previous Value was " & preValue & Chr(10) & "Revised " & Format(Date, "mm-dd-yyyy") & Chr(10) & "By " & Environ("UserName") 'Adds a new comment with the text. CHR(10) is a return.




    What i really want is to copy this comment in the above code and insert it into another cell on a different worksheet. So with each cell that change so does that comment and i need those comments pasted into a cell on a different worksheet.

  • Re: Copy Cell Comments Automatically To Another Workbook


    centaur63 - ermm ... seems Your not listening so i will try again. try answering my question please, does book2 exist? I ask as Your bugging at run-time.


    Saying another WorkBook does not help i know that already, what i need to know is the true destination WorkSheet by its ready name ie Code Name in VBA as that never changes even if You edit the Tab Name


    jiuk