automatically update comments with info. from another sheet

[auntie bella]

Hi Guys,
I have some code that does a lookup from sheet1 to sheet2 and updates the sheet1 comments from the sheet2 comments.

Is it possible to make it so that if I update a comment on sheet2 it will automatically update the comments on sheet1? Any pointers gratefully received.

Here is my code so far - I am happy for info on any flaws or errors you might notice. (I'm trying to teach myself and this took me ages! - quite embarrassed at how little there actually is!! )



Sub CommentAddOrEdit()
Dim x
Dim y
Dim Found1 As Range
Dim Found2
Dim cmt

' get contents of current cell
Set Found1 = ActiveCell

' goto next sheet
Sheets("Sheet2").Activate

' find matching cell and get the comment
Set Found2 = Sheets("Sheet2").Rows("1:1").Find(What:=Found1.Value, LookAt:=xlWhole)
Application.Goto Reference:=Found2
cmt = Found2.Comment.Text

' go back to 1st sheet
Sheets("Sheet1").Activate

'create comment based on comment found on sheet2

If ActiveCell.Comment Is Nothing Then
ActiveCell.AddComment
ActiveCell.Comment.Text cmt

Else

ActiveCell.Comment.Delete
ActiveCell.AddComment
ActiveCell.Comment.Text cmt

End If

End Sub

many thanks

 

[saltkev]

Hi

It seems to work when run via Worksheet_SelectionChange event, however I think you will need to use INTERSECT to limit the range of when this event takes place, otherwise it will run everytime you move between cells. Don't know if this is any use to you.




</PRE>

<CODE>regards</CODE>


</PRE>

<CODE>Saltkev</CODE>


</PRE>

 

[shg]

Adding a comment doesn't fire the Change event, so there's no way to know. You'd have to read all the comments, and figfure out where the corresponding comment goes in Sheet1.

You don't need all that Selecting:

Sub CommentAddOrEdit()
    Dim rFind As Range
 
    With ActiveCell
        If Not .Comment Is Nothing Then .Comment.Delete
        Set rFind = Worksheets("Sheet2").Rows(1).Find(What:=.Value, LookAt:=xlWhole)
        If Not rFind.Comment Is Nothing Then
            .AddComment.Text rFind.Comment.Text
        End If
    End With
End Sub

 

[auntie bella]

Many thanks.... I can see from your code how "sharp" it should be.
I tend to go the long way around so I can stop it at intervals and ensure it is returning the values I want.

If changing comments doesn't fire the Change EVent then I'll try to get a button on sheet1 so that after I update comments on sheet2 I can run through a loop to update the range of cells in sheet1 - I'll have a go myself (and no doubt be back later )

Again - many thanks