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

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
683
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
.
.

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

Code:
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
 
Upvote 0
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
 
Upvote 0
.
.

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

Code:
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
 
Upvote 0
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.
 
Upvote 0
.
.

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top