I would like to use a connector (like msoConnectorElbow), but without any
shapes in the project. I want to align the connector (beginning and end
points) to corners of cells.
The connector object needs coordinates in points. Is there a way to generate
the x,y points based on the cell that I want to align it with?

Thanks!
dan

Dan,
Try this.
The help is somewhat misleading, as it states:
"EndX Required Single. The horizontal position (in points) of the
connector's end point relative to the upper-left corner of the document."

However, it seems that it is really relative to the start X and Y, not the
document.
You could add another argument to the function to dictate the type of
alignment required; this just assume right and left, centred.

Private Type RECT
x1 As Single
y1 As Single
x2 As Single
y2 As Single
End Type

Private Sub CommandButton1_Click()

Call AddConnectorByRanges(Range("B4"), Range("H22"))

End Sub


Public Function AddConnectorByRanges(StartRange As Range, _
EndRange As Range, _
Optional ConType As MsoConnectorType
= msoConnectorElbow) _
As Boolean
Dim Coords As RECT

With StartRange
Coords.x1 = .Left + .Width
Coords.y1 = .Top + .Height / 2
End With

With EndRange
Coords.x2 = .Left - Coords.x1
Coords.y2 = (.Top + .Height / 2) - Coords.y1
End With

With Coords
ActiveSheet.Shapes.AddConnector ConType, .x1, .y1, .x2, .y2
End With

End Function

NickHK

"Dan" wrote in message
...
I would like to use a connector (like msoConnectorElbow), but without any
shapes in the project. I want to align the connector (beginning and end
points) to corners of cells.
The connector object needs coordinates in points. Is there a way to
generate
the x,y points based on the cell that I want to align it with?

Thanks!
dan

Thanks Nick!
On a related note... how to set the width of a connector (msoConnectorElbow)?
I tried .. connectorFormat.width = x

and similar things. Can't find anything in the connector properties reference.

Dan

"NickHK" wrote:

Dan,
Try this.
The help is somewhat misleading, as it states:
"EndX Required Single. The horizontal position (in points) of the
connector's end point relative to the upper-left corner of the document."

However, it seems that it is really relative to the start X and Y, not the
document.
You could add another argument to the function to dictate the type of
alignment required; this just assume right and left, centred.

Private Type RECT
x1 As Single
y1 As Single
x2 As Single
y2 As Single
End Type

Private Sub CommandButton1_Click()

Call AddConnectorByRanges(Range("B4"), Range("H22"))

End Sub


Public Function AddConnectorByRanges(StartRange As Range, _
EndRange As Range, _
Optional ConType As MsoConnectorType
= msoConnectorElbow) _
As Boolean
Dim Coords As RECT

With StartRange
Coords.x1 = .Left + .Width
Coords.y1 = .Top + .Height / 2
End With

With EndRange
Coords.x2 = .Left - Coords.x1
Coords.y2 = (.Top + .Height / 2) - Coords.y1
End With

With Coords
ActiveSheet.Shapes.AddConnector ConType, .x1, .y1, .x2, .y2
End With

End Function

NickHK

"Dan" wrote in message
...
I would like to use a connector (like msoConnectorElbow), but without any
shapes in the project. I want to align the connector (beginning and end
points) to corners of cells.
The connector object needs coordinates in points. Is there a way to
generate
the x,y points based on the cell that I want to align it with?

Thanks!
dan

Dan,
Recording a macro of the formatting I got:

Sub Macro1()
ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 169.5, 58.5, 132#,
132.75).Select
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 1.25
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.ConnectorFormat.Type = msoConnectorElbow
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.ShapeRange.Line.BeginArrowheadLength =
msoArrowheadLengthMedium
Selection.ShapeRange.Line.BeginArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.BeginArrowheadStyle = msoArrowheadNone
Selection.ShapeRange.Line.EndArrowheadLength = msoArrowheadLengthMedium
Selection.ShapeRange.Line.EndArrowheadWidth = msoArrowheadWidthMedium
Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadNone
End Sub

NickHK


"Dan" wrote in message
...
Thanks Nick!
On a related note... how to set the width of a connector
(msoConnectorElbow)?
I tried .. connectorFormat.width = x

and similar things. Can't find anything in the connector properties
reference.

Dan

"NickHK" wrote:

Dan,
Try this.
The help is somewhat misleading, as it states:
"EndX Required Single. The horizontal position (in points) of the
connector's end point relative to the upper-left corner of the
document."

However, it seems that it is really relative to the start X and Y, not
the
document.
You could add another argument to the function to dictate the type of
alignment required; this just assume right and left, centred.

Private Type RECT
x1 As Single
y1 As Single
x2 As Single
y2 As Single
End Type

Private Sub CommandButton1_Click()

Call AddConnectorByRanges(Range("B4"), Range("H22"))

End Sub


Public Function AddConnectorByRanges(StartRange As Range, _
EndRange As Range, _
Optional ConType As
MsoConnectorType
= msoConnectorElbow) _
As Boolean
Dim Coords As RECT

With StartRange
Coords.x1 = .Left + .Width
Coords.y1 = .Top + .Height / 2
End With

With EndRange
Coords.x2 = .Left - Coords.x1
Coords.y2 = (.Top + .Height / 2) - Coords.y1
End With

With Coords
ActiveSheet.Shapes.AddConnector ConType, .x1, .y1, .x2, .y2
End With

End Function

NickHK

"Dan" wrote in message
...
I would like to use a connector (like msoConnectorElbow), but without
any
shapes in the project. I want to align the connector (beginning and
end
points) to corners of cells.
The connector object needs coordinates in points. Is there a way to
generate
the x,y points based on the cell that I want to align it with?

Thanks!
dan