Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Connected shapes (Excel W2K03)

    Greetings loungers

    I am attempting to trace a series of connected shapes.
    Using the connector property and connectorFormat enables this.
    The problem is that it seems the connector shapes are evaluated in their creation order.

    I would prefer to establish recursively
    if a shape has an attached connector (and the number of connectors)
    ... then if so establish the end point of that connector and grab the text

    loop

    IT is almost as if there is a need for an "IsConnected" function.

    Thanks

    Geof


    Sub ConnectionEnd()
    For Each oShp In ActiveSheet.Shapes
    If oShp.Connector Then
    With oShp.ConnectorFormat
    If .EndConnected Then
    Set endshape = .EndConnectedShape
    endshape.Select
    MsgBox (Selection.Name)

    End If
    End With

    End If
    Next
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Connected shapes (Excel W2K03)

    The following is very rough and can no doubt be improved significantly, but it might help you on your way:

    Sub TraceConnectors()
    FindLastConnectorText ActiveSheet.Shapes(1)
    End Sub

    Sub FindLastConnectorText(shp As Shape)
    Dim shp1 As Shape, shp2 As Shape
    Set shp1 = shp
    Do
    Set shp2 = GetConnector(shp1)
    If Not shp2 Is Nothing Then
    Set shp1 = GetConnected(shp2)
    Else
    MsgBox shp1.TextFrame.Characters.Text
    Set shp1 = Nothing
    End If
    Loop Until shp1 Is Nothing
    End Sub

    Function GetConnector(shp As Shape) As Shape
    Dim con As Shape
    For Each con In ActiveSheet.Shapes
    If con.Connector Then
    If con.ConnectorFormat.BeginConnectedShape.Name = shp.Name Then
    Set GetConnector = con
    Exit For
    End If
    End If
    Next con
    End Function

    Function GetConnected(con As Shape) As Shape
    ' con is assumed to be a connector!
    If con.ConnectorFormat.EndConnected Then
    Set GetConnected = con.ConnectorFormat.EndConnectedShape
    End If
    End Function

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Re: Connected shapes (Excel W2K03)

    Hello Hans
    Thanks for this.
    It will take a bit to get my head around it.

    I appreciate the start.

    Geof

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •