Show cell value(text) in comment box text, or mouse tool tip on an gif icon

Roy, thanks for answering my post.

Roy can you guide me how can I link a cell comment to a cell value? This can be very useful for any project saving space in worksheets. What is the object for the comments text box, and how do you refer it?

Hello JIBG,

Great idea using the comment like that. Turns out to be simple to do to. Here is a UDF (User Defined Function) that will let you Link a Comment's Text with the Text of Another Cell. The first argument is the cell that contains the text you want to display in the comment. The Second argument is the address of comment cell.

Example of UDF call

Cell B1 contains the text for the comment
Cell C1 has a comment

The formula in cell C1 is =LinkComment(B1, C1)

Place this UDF code in a Standard VBA Module. You can then use the UDF in any cell, on any sheet in the workbook.

UDF Code

Function LinkComment(Text_Cell As Range, Comment_Cell As Range)

  Dim X As Variant
  
    Application.Volatile
  
      X = Comment_Cell.Comment.Text(Text_Cell.Text)
      LinkComment = ""
    
End Function

Sincerely,
Leith Ross

Leith, thanks for the replay.
You are a life (..and space) saver!
I will be using this method very often in future projects. It's nice to have information not needed visually in a busy, data full sheet when I can store this data in another sheet and with the magic of the mouse show it and make it go away! specially when you can make it dynamic in the other sheet (who said that tool tip only works in a Form) this is the workaround.


Any way thanks again!
Leith try the function on a new workbook (i stock a little bit first, because I wasn't inserting a comment first) and worked very well, then I tried in the intended workbook and wow! works very well. One more question; can this be use to link a range? for example use it to show several names in one comment? I tried it that way and didn't work when I highlighted several cell. Any ideas?

Hello JIBG,

This version will take each cell in the selection, and add it to comment as a single line.

Function LinkComment(Text_Cell As Range, Comment_Cell As Range)

  Dim C As Range
  Dim Txt As String
  Dim X As Variant
  
    Application.Volatile
  
      For Each C In Text_Cell
        Txt = Txt & C.Text & vbCrLf
      Next C

      X = Comment_Cell.Comment.Text(Txt)
      LinkComment = ""
    
End Function

Sincerely,
Leith Ross