PDA

View Full Version : [SOLVED] How to Format Comment Connector Line



Cyberdude
03-24-2005, 10:10 AM
I have to FAX a set of data every month, and the data has some comments which are visible and moved to the right so they don't cover the data values. The "connector line" (I don't know what else to call it) that goes from the comment box to the cell it refers to is VERY narrow and the FAX often doesn't reproduce it. As a result, the FAXed copy has a bunch of comments without a way to tell which cells they are refering to. So far I have not found a way to increase the thickness of the comment connector line or the size of the arrow head at the end of it. Can anyone tell me how to do that? There IS a way to increase the thickness of the line around the comment box ... I know how to do that. The connector line seems to be an independent element. :banghead:

Killian
03-24-2005, 07:38 PM
I haven't found a way of accessing the connector line either so it looks like you'll have to draw over the top of them all.
With code, of course! Here's some code to draw them:


Sub DrawNewConnectors()
Dim c As Comment
Dim cl As Single, ct As Single, sl As Single, st As Single
Dim newconn As Shape

For Each c In ActiveSheet.Comments
cl = c.Parent.Left + c.Parent.Width
ct = c.Parent.Top
sl = c.Shape.Left
st = c.Shape.Top
Set newconn = ActiveSheet.Shapes.AddLine(sl, st, cl, ct)
With newconn
.Line.Weight = 2
.Line.EndArrowheadStyle = msoArrowheadTriangle
.Name = "newconn" & .ID
End With
Next
End Sub

and some to delete them after


Sub DeleteNewConnectors()
Dim s As Shape
For Each s In ActiveSheet.Shapes
If Left(s.Name, 7) = "newconn" Then s.Delete
Next
End Sub

/EDIT I just noticed that this assumes the connector joins the comment at its top left corner, which it may not, but I don't see a way of detecting the connector position so you may have to live with that limitation

Cyberdude
03-25-2005, 09:56 AM
Thanx loads, Killian!