vba to link the 'comment' in one cell to the 'contents' of an another cell

[cjcass]

Hi,

Does anyone know of any vba that will dynamically link the comment in one cell to the contents of another cell?

eg. cell A1 has the word "Apples" written into it and cell A2's comment is "Apples" reading from the contents of A1. If I then type "Pears" into A1 the comment in cell A2 also changes to "Pears"... Please note cell A2 has a formula in it which is completely independent from this requirement.. I only want A2's comment to change.

Any help much appreciated.
Rgds,
Chris

 

[ParamRay]

.
.

You can use this worksheet change event, which must be placed in the code module corresponding to that particular worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyComment As Comment
    If Not Intersect(Target, Me.Range("a1")) Is Nothing Then
        On Error Resume Next
        Set MyComment = Me.Range("b1").Comment
        If Err.Number <> 0 Then
            Set MyComment = Me.Range("b1").AddComment
        End If
        On Error GoTo 0
        MyComment.Text Me.Range("a1").Text
    End If

End Sub

 

[cjcass]

Hi GP,
Many thanks for the code it works well... Since I now have 6 cells across 6 columns with which I want them all to act in the same way... I tried to add extra code to cover off the extra cells but it's not playing (see below..), any ideas on how to get the syntax correct?
Thanks again,
Chris

Dim MyComment As Comment
If Not Intersect(Target, Me.Range("T9")) Is Nothing Then
On Error Resume Next
Set MyComment = Me.Range("T7").Comment
If Err.Number <> 0 Then
Set MyComment = Me.Range("T7").AddComment
End If
On Error GoTo 0
MyComment.Text Me.Range("T9").Text

If Not Intersect(Target, Me.Range("W9")) Is Nothing Then
On Error Resume Next
Set MyComment = Me.Range("W7").Comment
If Err.Number <> 0 Then
Set MyComment = Me.Range("W7").AddComment
End If
On Error GoTo 0
MyComment.Text Me.Range("W9").Text
End If
End If

 

[ParamRay]

.
.

Sorry CJ, there was an error in my code. Thanks for pointing it out. Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyComment As Comment
    If Not Intersect(Target, Me.Range("t9")) Is Nothing Then
        With Me.Range("t7")
            On Error Resume Next
            .AddComment
            On Error GoTo 0
            .Comment.Text Me.Range("t9").Text
        End With
    End If
    If Not Intersect(Target, Me.Range("w9")) Is Nothing Then
        With Me.Range("w7")
            On Error Resume Next
            .AddComment
            On Error GoTo 0
            .Comment.Text Me.Range("w9").Text
        End With
    End If

End Sub

 

[cjcass]

Brilliant!! thanks GP... works great

 

[AcidGeek]

Hi,

Great thread, almost exactly what I want, but I'd like to change an entire column (500 cells). (Forgive me if this is super simple - I'm relatively new to VBA)

I'd like to add a comment to CA2, on the change of CA2, the comment will be in CB2 (Index Match from CA2 and hidden from view)

How do I extend the code to cells CA2-CA501??

Kind Regards,
AG.

 

[Khin Zaw Htwe]

.
.

Sorry CJ, there was an error in my code. Thanks for pointing it out. Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyComment As Comment
    If Not Intersect(Target, Me.Range("t9")) Is Nothing Then
        With Me.Range("t7")
            On Error Resume Next
            .AddComment
            On Error GoTo 0
            .Comment.Text Me.Range("t9").Text
        End With
    End If
    If Not Intersect(Target, Me.Range("w9")) Is Nothing Then
        With Me.Range("w7")
            On Error Resume Next
            .AddComment
            On Error GoTo 0
            .Comment.Text Me.Range("w9").Text
        End With
    End If

End Sub

Thank a lot for your codes. If reference cell (t9/w9) is in other sheet of same workbook, may I know how to write. Thank You.