Display the TEXT of a comment in a new cell

  • Thread starter Thread starter wojo
  • Start date Start date
W

wojo

Is there a way to display the comment, attached to a cell, as the
'value' of the cell?

I have copied/pasted the comments to a new form (sheet) and would like
to have the comment text displayed IN the new cell. Then I can print
the sheet.

My current macro copies all comments from one sheet and pastes them
onto a second sheet. Now the new sheet shows the comment indicator,
with a blank cell. I want to replace the blank cell, with the TEXT of
the comment. Can this be done?

Thank you in advance.

JO
 
Hi wojo,

You could try this user-defined function placing it in a standard module of
your workbook:

Function GetCommentText(myCell As Range) As String
On Error Resume Next
GetCommentText = myCell.Cells(1, 1).Comment.Text
End Function

and then use the following formula on the spreadsheet:

=GetCommentText(A1)

where A1 is the cell which has the comment to copy.

Regards,
KL
 
I'm fairly 'self taught' in Excel. I will digest your solution, give
it a try and respond here. Thanks in advance, wish me luck.
 
Here's what I entered in the module. I get an "error"

Fuction GetCommentText(myCell As Range)As String
On Error Resume Next
GetCommentText = myCell.Cells(1, 1).Comment.Text
End Function

Am I supposed to replace anything above, with "my" spreadsheet info?

Jo
 
I still get the error, "Compile error Expected list seperator or )" ,
after I fixed my typo. Jo
 
Hi wojo,

I am not sure what else could cause the error you get. I copied the code
from your message, corrected the word Function and it works well. Try maybe
do the same copy-paste directly from the message.

Regards,
KL
 
sorry didn't mean to send anything,
I can't duplicate your error, even tried putting in a comma or a semicolon
to separate non-existent additional arguments.

The function presented
works for me, check that you only have a cell reference in your
invocation. =GetCommentText(A14)
=personal.xls!GetCommentText(A14)
that it was installed as a regular macro i.e. Module1
which could be renamed later, but not one originally named like Class1
 
The comment is "attached" to cell F43. I put the function in Module 1,
then put

=GetCommentText(F43)

In cell F43. Is that where it goes? I don't get an error now, but I
don't get the text of the attached comment as the Value of F43.

I'm sure it's something silly, but I still need more guidance. Thanks
everyone.

Jo
 
Are you sure that F43 actually contains a comment?

If you hit Insert (on the menubar), do you see "Edit Comment" or "Insert
Comment".

If you see "insert comment", then that cell doesn't have a comment--maybe it's
the data|validation input message.

If you do see "edit comment", try hitting F2 and then enter (with F43 selected).

Did that help?

If that's the case, I'd modify the function slightly:

Function GetCommentText(myCell As Range) As String
application.volatile '<---
On Error Resume Next
GetCommentText = myCell.Cells(1, 1).Comment.Text
End Function

Changing comments won't cause the GetCommentText function to reevaluate. By
pretending to change the cell, xl saw that the cell "changed" and reevaluated
the function.

By adding "application volatile", this function will reevaluate whenever excel
recalculates--so it could be one recalc behind.
 
I think I figured out the problem. I am entering the =GetComment line
in the same cell as the cell with the attached comment. Can I do this,
or does it need to go into a different cell?

Jo
 
It worked in the same cell.

I think it's a sequencing problem--when you change the comment and when excel
knows to recalc the formula.
 
I'm currently away from my computer, so I can't try your solution until
Monday evening. I will let you know what I discover. Jo
 
Ok, Don't know why, but the Function is working now. Now, if only I
could LINK the comment.

Right now, the comment is on one sheet and I copy/paste
special/comments (using a macro) to a Form. Then I have another macro
that sorts the information.

One last question, regarding the Comments.
Is there a way to show the TEXT of the comment, without the "personal
copy" that is 'attached' to the comment. I believe if I was at work,
the " " would be the name of the user that entered the information.
This part of the comment is not necessary. I have a small amount of
room to paste the text of the comment and would like to also limit the
size of the comment to 25 characters.

Can this be done?

Thanks to all that helped with this problem.

Jo
 
Sorry, I forgot to mention, I would still like the 'attached' "name" to
be present in the comment, but would like to have it deleted on the
Form, where I extracted the text and placed it into the cell.

Example: Comment
Personal Copy:

I want the cell to display Only the information after the : (semi
colon)

Everyone has been so helpful, I hope I'm not asking too much.

Jo
 
From your earlier reply...

I'm not sure what Link means the way you use it.

But this is one way to ignore the stuff before the colon:

Option Explicit
Function GetCommentText(FCell As Range) As Variant
Application.Volatile

Dim ColonPos As Long
Dim myStr As String

Set FCell = FCell(1)

If FCell.Comment Is Nothing Then
GetCommentText = ""
Else
myStr = FCell.Comment.Text
ColonPos = InStr(1, myStr, ":")
myStr = Trim(Mid(myStr, ColonPos + 1))
If Left(myStr, 1) = vbLf Then
myStr = Mid(myStr, 2)
End If
GetCommentText = myStr
End If

End Function
 
Finally, I got this to work. I'm not really sure how, I didn't change
anything. Maybe it just 'happened' when I put my cursor back in the
original cell with the comment and then entered. The 'text' of the
comment is now right where I want it to be.

Now, if only Excel would allow us to 'link' comments, along with a
cell. That would be great. Right now, I am playing with macros to
copy/paste special, to the new form, before the user prints.

Thanks bunches to all that helped.

Jo
 
The UDF could be one calculation cycle behind.

Changing the comment doesn't make the function recalculate. But if you've added
that "application.volatile" line, then whenever excel calculates, your UDF will
be recalced.

But by making a change to the cell (even just F2, then enter), excel thought
that the UDF should recalc.
 
I changed the function, to your code above. This does a wonderful job
of deleting the user's name and the colon -THANKS!!!!!!

This particular worksheet was created for printing information only.
There are no calculations, The good news is, I have a button on the
main worksheet, this button, updates the information on the form and
does the same thing as calculating.... the comments ARE updated (thanks
to you and others.)

BTW... this is the same form that I am trying to sort. I'm still
working on that.

What would we do without helpers like you? I shudder to think!

Jo
 
Back
Top