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

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.


Bijoy V J wrote:

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

Dear Dave,

A million thanks for the quick reply.

Cheers,

VJ

"Dave Peterson" wrote:

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.


Bijoy V J wrote:

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

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.


"Dave Peterson" wrote:

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.


Bijoy V J wrote:

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

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

On Wed, 23 Jul 2008 12:34:03 -0700, confused
wrote:

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.


"Dave Peterson" wrote:

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.


Bijoy V J wrote:

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

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!

"Gord Dibben" wrote:

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

On Wed, 23 Jul 2008 12:34:03 -0700, confused
wrote:

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.


"Dave Peterson" wrote:

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.


Bijoy V J wrote:

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

I don't understand how it caused an error.

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

confused wrote:

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!

"Gord Dibben" wrote:

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

On Wed, 23 Jul 2008 12:34:03 -0700, confused
wrote:

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.


"Dave Peterson" wrote:

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.


Bijoy V J wrote:

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




--

Dave Peterson