Linking Comment to Cell

[Shaun]

I need to link information found in other sheets of my workbook to comment
boxes on my first sheet. Can this be done, and if so, how?? Any help is
much appreciated, thanks.

Shaun

 

[Gary''s Student]

Try this UDF:

Function copycomment(r As Range)
Application.Volatile
Dim whereami As Range
Set whereami = Application.Caller
If whereami.Comment Is Nothing Then
Else
whereami.Comment.Delete
End If
whereami.AddComment Text:=r.Value
copycomment = ""
End Function

 

[Shaun]

Thank you very much, but I think this may be more technical than I am.
UDF?
What do I do with this code?

Shaun

 

[Gary''s Student]

Putting the code in is easy, just copy and paste from my post. See:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

By the way, with a small modification, the function can put the value in a
cell into any other cells's comment, not just the cell containing the
function.

This is an exception to the general rule about a function only returning a
value to a single cell.

 

[Gord Dibben]

Shaun

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste GS's CopyComment code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Enter the formula in Sheet1 in the cell you want the comment in.

=CopyComment(Sheet2!A4)


Gord Dibben Excel MVP

Thank you very much, but I think this may be more technical than I am.
UDF?
What do I do with this code?

Shaun

Gord Dibben MS Excel MVP