Create Cell Comment based on text in a cell on another worksheet

[Guest]

I have comments on all of my products stored in a separate worksheet. Is it
possible to add a comment to a product name cell that automatically links to
the text in that other worksheet? Either a direct cell reference or a table
lookup would work, but I don't see any way to put a formula into a comment.

 

[Guest]

http://www.microsoft.com/office/com...&p=1&tid=eaac92bf-205c-49c6-be59-bb11a6048134

 

[Guest]

Try this inserted into the sheet where you enter the product name. I have
assumed products are entered in a series of cells in column A. If it only one
cell then change Target.Column = 1 to Target.Address = "$A$10" (or whatever
address is).

Right click on tab, view code and copy and paste

Table of Product and its corresponding comment are on Sheet2, columns A & B.

If no Product is found, then no comment is added.

HTH



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim res As Variant
On Error GoTo ws_exit
Application.EnableEvents = False
Set rng = Worksheets("Sheet2").Range("A:B") '<=== Product/Comment table
in Sheet2
If Target.Column = 1 Then ' <=== Data entered in Column A
res = Application.VLookup(Target.Value, rng, 2, 0)
If Not IsError(res) Then
Target.AddComment
Target.Comment.Text Text:=res
End If
End If
ws_exit:
Application.EnableEvents = True

End Sub