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

[JIBG]

I have a spread sheet were the area is getting very limited. I need to insert a small icon and when the mouse goes over (like it does in a form tool tip) will show the value of a cell (text value) located in another sheet in same workbook, or I was thinking inset a comment next to the icon and link the comments of the comments text to cell with the text value.

I've look the properties of this to objects and can figure it out.

Any help will be greatly appreciated.

[royUK]

I think you will only be able to do this with VBA.

[JIBG]

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?

[Leith Ross]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[JIBG]

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?

[Leith Ross]

Hello JIBG,

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

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[JIBG]

Leith, I can only say one thing

Thanks!

The function works great! what a useful function, I know I will be using it a lot.


Thanks to all in this newsgroup, for making this great community.

Sharing the knowledge makes people more efficient, thank guys for sharing your knowledge!

[JIBG]

Leith, I been using this function a lot and very effectively, I can believe I can pack so much information in one cell (Charts, data range, etc).

Ones again thanks for the code.

I got one question how can I make the Function be dynamic as it is when I linked to only one cell? ....When I link the Function with one cell, and the value (text)in this cell is changed in the "linked cell" the comment text on the cell were I inserted the Function changes, but when I use it in a range and I change some of the values of the range it doesnt "update" the value of the comment text.

Also one more question on Application.Volatile; is this (volatile) requiered if you only use text values in cells?

[Leith Ross]

Hello JIBG,

Glad you like it. I am not sure I follow what you mean with applying the function to Range. It is constructed to work with a single cell value. Can you give me an example of the range application?

The Application.Volatile statement is used in VBA macro to ensure the macro fires whenever Excel recalculates. This is used only with a UDF macro. The same macro will run in VBA. The Application.Volatile statement will be ignored.

Sincerely,
Leith Ross

[JIBG]

Leith,
Thanks again for the replay. I was able to troubleshoot what was the problem; I have a range linked with the Function, but this column is the concatenation of 5 columns, and I was inserting a space between the cell name and the concatenation character “&”, after removed the space everything work fine. Now that I found the root cause of that problem, I got another question;
How can I remove the space character (□) in this Function, when I link the data (all of this data are string word) the character appears in every empty cell or in a carriage return.
See attached

Thanks for you help!

[Leith Ross]

Hello JIBG,

There a coupe o ways to remove unwanted characters from a string.

Please Login or Register  to view this content.

Siincerely,
Leith Ross

[DrMatt]

I am not sure what I am doing wrong but I put the below function code in by right clicking the sheet tab and view code then pasted it into the cade area.

Please Login or Register  to view this content.

I then placed the =LinkComment(B1, C1) into a cell C1 on the sheet. I get #NamE? in c1. Any help would be greatly appreciated.

[Kenneth Hobson]

Please read the instructions.

Place this UDF code in a Standard VBA Module.

[arlu1201]

DrMatt,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

[DrMatt]

Thanks Ken

[DrMatt]

Great. Will do.

[DrMatt]

With this code if I change the text in the cell the comment does not update. Is this the way it is supposed to work? If so is there a way that it will update if the original text in the cell is updated?

[DrMatt]

When I change the text in the original cell it does not update in the comment. Is this the way it is supposed to work? If yes, do you know how i can have the comment update when the original text is updated?

[smarienstras]

Hello,

I used this function with success in excel 2010.
I migrated to excel 2013 and its not working anymore :
The comment appears and right away disappear when I hoover over the cell with the mouse.

Any clue ?
Thanks indeed in advance !