Comment fields and links...
Posted by Hansoh on February 11, 2002 7:48 AM
if i reference a cell from an adjoining sheet (let's say sheet2) using a simple =sheet1A1 and that referenced cell has a comment attached to it, is there a way to bring view that comment on sheet2?
what if i reference that cell in sheet1 using an INDIRECT function in sheet2?
also, in PAGE SETUP, in the SHEET tab, i can choose to view COMMENTS in several ways. one way is 'AT END OF SHEET'. i can view those comments in PAGE PREVIEW, but those comments don't reside anywhere on the viewable spreadsheet, do they? do those comments have some kind of address so i can reference them in a macro? please advise. thanks in advance.
-han
Posted by Damon Ostrander on February 11, 2002 1:48 PM
Hi Hansoh,
These are some good questions. The answer to your first
question is no, and yes. There is no way I know of
using built-in Excel functions or capabilities to
indirectly reference a comment. That's the "no" part
of the answer. To be able to indirectly reference a
comment would be like indirectly referencing the cell's
formatting (color fill, for example).
But it is possible with VBA code to
display the referenced cell's comment in a variety of
ways. For example, a macro could be written that,
when the worksheet calculates, places all referenced
cell comments into the cell that cell that contains
the reference. Another possibility is that, whenever
a cell is selected, it checks to see if that cell
references another cell that contains a comment and
adds that comment to the selected cell. With this
approach you could have the macro automatically
the comment when the cell is deselected. Yet another
possibility is to display the referenced cell's
comment in Excel's statusbar line or pop up a textbox
that contains the comment, but delete it as soon as
another cell is selected.
Regarding your page setup question, even when you
print/view comments at the end of the sheet, those
comments still reside on that sheet and are associated
with that sheet and with the cells that contain them.
So yes, comments have an address because they are
a sub-object of the range object (cell or cells)
within which they reside. For example, it you want
the comment associated with cell B4 on the active
worksheet, you could reference its text in a macro
as:
Range("B4").Comment.Text
or just
[B4].Comment.Text
Within VBA one can access and set all the properties of
comments, including fill color, font characteristics,
margins, size, etc. One can also add and delete comments.
Because each worksheet had a Comments collection object,
it is also easy to find all the comments on a worksheet,
and determine which cells they reside in.
I hope this helps. I didn't provide any examples,
but could if you had a particular way in mind that
you want to display the comments. If you just want
to display the comment as the cell contents using
a comment-retrieval function, this would be very
easy, but some of the other options are a bit more
involved.
I hope this helps.
Damon
