Programming Excel with VBA and .NET

Connect Shapes

To draw lines between two shapes:

1. Use the AddConnector method to create a connector shape.

2. Use the connector shape’s ConnectorFormat property to establish the connection.

Connectors attach to connection sites on a Shape object and maintain the connection even if you drag the objects to another location. The following code creates the two connected rectangles shown in Figure 18-7:

Sub ConnectShapes( )
    Dim ws As Worksheet, s1 As Shape, s2 As Shape, conn As Shape
    Set ws = ActiveSheet
    ' Draw rectangle
    DrawRect
    ' Get a reference to new rectangle (last object in Shapes collection)
    Set s1 = ws.Shapes(ws.Shapes.Count)
    ' Repeat for second rectangle.
    DrawRect
    Set s2 = ws.Shapes(ws.Shapes.Count)
    ' Move the second rectangle.
    s2.IncrementLeft 100
    s2.IncrementTop 50
    ' Create a connector (position and size don't matter).
    Set conn = ws.Shapes.AddConnector(msoConnectorCurve, 1, 1, 1, 1)
    ' Connect to each rectangle.
    conn.ConnectorFormat.BeginConnect s1, 3
    conn.ConnectorFormat.EndConnect s2, 2
End Sub

Tip

ConnectShapes reuses the DrawRect example shown previously.

The second argument for BeginConnect and EndConnect determines where the connector attaches to the shape. For most shapes, connection sites are numbered counter-clockwise on the shape starting at the top, as shown in Figure 18-8.

Figure 18-7. Connected shapes stay connected

Figure 18-8. Connection site numbering

To establish the shortest ...