Sorry Bernie, but this isn't what i'm looking for.

Assume the user has made the comment visible on the worksheet and that they
have then selected the textbox that displays the popped up comment (i.e.,
TypeName(Selection) = "TextBox" and Selection.ShapeRange.Type = msoComment).

How do I get from Selection back to the Comment?

I suppose I could itterate through all of the worksheet's comments and match
the contents of the TextBox with that of each comment ... but if the
worksheet contained two identical comments then my search might identify the
wrong comment.

It just seems like there must be some way to work backward through the
object model to get from the selected comment to the comment object itself.

Is there someway to find all of the little connectors on the worksheet? If
so, maybe I could find the little line with the arrow head on it that points
from the comment's textbox back to its cell. If I could find those
connectors then I could look for one that has an endpoint on the comment's
textbox and then use the other endpoint to identify the cell the comment is
associated with and then I'm there.

Does the above make the problem clearer?

Thanks,

josh





"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Josh,

The comment's parent is a range object, which you can find like so:

Sub test()
Dim myComm As Comment
For Each myComm In ActiveSheet.Comments
MsgBox "The comment for cell " & myComm.Parent.Address & _
" has the text:" & Chr(10) & """" & myComm.Text & """"
Next myComm
End Sub

HTH,
Bernie
MS Excel MVP


"Josh Sale" <jsale@tril dot cod wrote in message
...
I'm trying to figure out how to navigate from a textbox that contains a
comment object back to the worksheet cell that the comment is associated
with.

The case begins with the user selecting the comment textbox
(TypeName(Selection) = "TextBox" and Selection.ShapeRange.Type =
msoComment) and then running one of my macro's to manipulate the comment.

The textbox's TopLeftCell and BottomRightCell are relative to where the
textbox got popped up (or was moved by the user) so they don't help.

I just can't figure out how to crossover in the Excel object model from
the textbox that is displaying the comment to the Range.Comment that
actually contains the comment.

Any suggestions?

TIA,

josh