Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
Josh,
Oops, I'm sorry that I misunderstood. Your explanation does make it clearer. Try MsgBox ActiveSheet.Comments(Selection.Index).Parent.Addre ss & " is the cell with that comment." Of course, you will need error checking etc. to make sure that the Selection is actually a comment or the code will blow up. HTH, Bernie MS Excel MVP "Josh Sale" <jsale@tril dot cod wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
That works great!!!
I have to admit to still being puzzled as to why it works. When Selection references a TextBox, why does Selection.Index correctly index into the worksheet's Comments collection? Don't get me wrong, I'll take the solution even if its black magic but I would like to flesh out my understanding of the Excel object model. Thanks so much!! josh "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Josh, Oops, I'm sorry that I misunderstood. Your explanation does make it clearer. Try MsgBox ActiveSheet.Comments(Selection.Index).Parent.Addre ss & " is the cell with that comment." Of course, you will need error checking etc. to make sure that the Selection is actually a comment or the code will blow up. HTH, Bernie MS Excel MVP "Josh Sale" <jsale@tril dot cod wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
Josh,
The index value is not referencing a textbox, it is referencing a comment (which is a special kind of textbox). Each comment has an index (And a name) and the icex can be used to reference the comment (but the name cannot). Of course the comment is a member of the comments collection. And that is why it works, and why it will blow up if your selection isn't a comment. HTH, Bernie MS Excel MVP "Josh Sale" <jsale@tril dot cod wrote in message ... That works great!!! I have to admit to still being puzzled as to why it works. When Selection references a TextBox, why does Selection.Index correctly index into the worksheet's Comments collection? Don't get me wrong, I'll take the solution even if its black magic but I would like to flesh out my understanding of the Excel object model. Thanks so much!! josh "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Josh, Oops, I'm sorry that I misunderstood. Your explanation does make it clearer. Try MsgBox ActiveSheet.Comments(Selection.Index).Parent.Addre ss & " is the cell with that comment." Of course, you will need error checking etc. to make sure that the Selection is actually a comment or the code will blow up. HTH, Bernie MS Excel MVP "Josh Sale" <jsale@tril dot cod wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding attachment point for comment textbox
Thanks.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I add a footnote or comment to a data point in a chart? | Charts and Charting in Excel | |||
how do I add a hidden comment to a point on an excel graph | Charts and Charting in Excel | |||
Fix a comment to a moving datum point in a line chart | Charts and Charting in Excel | |||
How do I add a comment to a data point in a chart? | Charts and Charting in Excel | |||
Positioning the Insertion Point in a TextBox | Excel Programming |