How can I link the text in a cell into a comment box?

[Guest]

Hi I want to add a comment to a cell. But the content of the comment box is
actually some text from another worksheet. Can I use a formula/link to
display the text instead of copying the text from the source worksheet to the
coment box manually? For example, I want to add a comment to cell A1 in
Sheet1. The content of the comment box should be the text in cell B1 in
Sheet2. The usual formula (=Sheet2!B1) doesnt work here. Thanks for the
replies in advance

 

[Dave Peterson]

You can use a User Defined Function.

You could have a UDF in A1 copy a comment from one cell to another.

Option Explicit
Function EchoComment(FCell As Range, TCell As Range)

If TCell.Comment Is Nothing Then
'do nothing
Else
TCell.Comment.Delete
End If

TCell.AddComment Text:=FCell.Text

EchoComment = ""

End Function


Then I could put this in any cell:
=echocomment(sheet2!b1,sheet1!a1)



If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel to test it out.

 

[Guest]

Dear Dave,

A million thanks for the quick reply.

Cheers,

VJ

 

[confused]

I'm sure it's user error but i can't get this to work.
If I, for example, want to add a comment to cells A3 thru A100 that will
display the contents off the corresponding cells by row in Z3 thru Z100 is
this the best (read simplest) method?
A3:A100 contain formulas, Z3:Z100 contain text.
Thanks in advance for any help or clarification you can offer.

 

[Gord Dibben]

Sub Comment_Add()
Dim cmt As Comment
Dim r As Range
For Each r In Range("A3:A100")
Set cmt = r.Comment
If cmt Is Nothing Then
Set cmt = r.AddComment
cmt.Text Text:=r.Offset(0, 25).Text
End If
Next r
End Sub


Gord Dibben MS Excel MVP

 

[confused]

Many thanks to Gord Dibben and Dave Peterson!!!
You guys are awesome.

Dave, I was stupidly inserting the code as an object on the specific sheet.
Thanks for the clafification. Great dynamic link for the comments (I'm sure
I'll be using it in the future) but it errored out if I resorted my sheet.

Gord, worked like a charm. Wish it was dynamic instead of macro but this is
fix i'm going with.

Thanks again!

 

[Dave Peterson]

I don't understand how it caused an error.

Maybe because the formulas don't point to the new location after the sort????

Many thanks to Gord Dibben and Dave Peterson!!!
You guys are awesome.

Dave, I was stupidly inserting the code as an object on the specific sheet.
Thanks for the clafification. Great dynamic link for the comments (I'm sure
I'll be using it in the future) but it errored out if I resorted my sheet.

Gord, worked like a charm. Wish it was dynamic instead of macro but this is
fix i'm going with.

Thanks again!

 

[confused]

i'm not sure either. it works at first but when i sort my sheet the cell with
=echocomment(FCell,TCell) resolves to #VALUE. if i edit the cell it then
resolves back to a blank and works again. The macro version will work for me
because i don't necessarily need it to be dynamic but if you're curious
here's an approximation of the pertinent info:

in sheet1 i have a table and i want the text in column AC to show as a
comment in column A.
I put your code in Module1
I entered "=echocomment(AC2,A2)" in AD2 and filled down.
everything was working, AD2:AD# resolved to a blank.
then i sorted the sheet and lost the comments in A2:A# and AD2:AD# resolved
to #VALUE.
by selecting AC2 and clicking in the formula bar (so excel thought i had
edited the cell) and filling down again AC2:AC# resolved back to a blank and
everything was working again.
I tried sorting columns A:AC and just columns A:AB and got the same result
then i tried entering "=echocomment(Sheet1!AC2,Sheet1!A2) in A2 of Sheet2
and filling down.
that acted the same way (resolved to blank and worked at first but then to
#VALUE when i did sorts on Sheet1).

Anyway, thanks again for the help. I'll check back to see if you crack it
(or if i've made another goofy and obvious error).
Cheers!

P.S. i can email you a sample if your dying to fix it and can't replicate
the behaviour described. just post me your e m a i l.

 

[Dave Peterson]

I don't know how you got the error.

I've tested this and can't duplicate it.

But if you're happy with the other subroutine, I'm not gonna worry about it.