Determine connector on shape and move the connector-begin (or end)

[mario1968]

Hi everybody,


I would like to write a VBA for moving the begin or end of a connector to the next possible anchor on the connected shape (clockwise or anticlockise; this is not relevant ).
The user shoud click on the shape (not on the connector) and than (when the VBA is processed) the connector shold jump to the next possible anchor on the selected shape.


I googled many hours As I am new to VBA I wonder if this is possible?


Thanke, many regards
Mario

 

[Domenic]

The following macro will move the anchor to the next higher one, so it will move it counter-clock wise. We can change it so it goes clock-wise, if you prefer. You'll need to change the name of the connector accordingly, and make sure that the connector is placed at the back of the z-order so that it doesn't interfere when clicking a shape. The macro must be placed in the code module for the sheet containing your shapes and connector. So right-click the sheet tab, select View Code, and paste your code there, in the code module for the sheet. Then, assign this macro to both your shapes.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] NextConnectionSite()

    [COLOR=darkblue]Dim[/COLOR] shpTarget [COLOR=darkblue]As[/COLOR] Shape
    [COLOR=darkblue]Dim[/COLOR] shpConnector [COLOR=darkblue]As[/COLOR] Shape
    [COLOR=darkblue]Dim[/COLOR] NewConnSite [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] shpTarget = Me.Shapes(Application.Caller) [COLOR=green]'clicked shape[/COLOR]
    [COLOR=darkblue]Set[/COLOR] shpConnector = Me.Shapes("Straight Arrow Connector 1") 'connector (change the name accordingly)
    
    [COLOR=darkblue]If[/COLOR] shpTarget [COLOR=darkblue]Is[/COLOR] shpConnector.ConnectorFormat.BeginConnectedShape [COLOR=darkblue]Then[/COLOR] [COLOR=green]'shape connected to the beginning of connector[/COLOR]
        [COLOR=darkblue]With[/COLOR] shpConnector.ConnectorFormat
            NewConnSite = .BeginConnectionSite + 1
            [COLOR=darkblue]If[/COLOR] NewConnSite > shpTarget.ConnectionSiteCount [COLOR=darkblue]Then[/COLOR]
                NewConnSite = 1
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            .BeginConnect shpTarget, NewConnSite
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]ElseIf[/COLOR] shpTarget [COLOR=darkblue]Is[/COLOR] shpConnector.ConnectorFormat.EndConnectedShape [COLOR=darkblue]Then[/COLOR] [COLOR=green]'shape connected to the end of connector[/COLOR]
        [COLOR=darkblue]With[/COLOR] shpConnector.ConnectorFormat
            NewConnSite = .EndConnectionSite + 1
            [COLOR=darkblue]If[/COLOR] NewConnSite > shpTarget.ConnectionSiteCount [COLOR=darkblue]Then[/COLOR]
                NewConnSite = 1
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            .EndConnect shpTarget, NewConnSite
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[mario1968]

Di Domenic,

looks very good.

But there is one thing that is not so nice. The name of the connector is hard coded:

ActiveSheet.Shapes("Straight Arrow Connector 1")

Do you have an idea how to get the name of the connector dynamically?

Many, many thanks

Regards
Mario

 

[Domenic]

One way would be to refer to it by index number. So, for example, if the connector is the third shape within the Shapes collection, you could refer to it like this...

Set shpConnector = Me.Shapes(3)

Although, a more reliable way would be to loop through each shape within your worksheet, and test whether it's a connector using the Connector property, like this...

    Dim shpConnector As Shape
    Dim shp As Shape
    
    For Each shp In Me.Shapes
        If shp.Connector Then
            Set shpConnector = Me.Shapes(shp.Name)
            Exit For
        End If
    Next shp

However, if you have more than one connector, the code will become a bit more complex. Is this the case?

 

[mario1968]

Hi Domenic ,

very good job; you are my hero! Thanks a lot!

Isn't there any relation between the connector and the connected object? I guess it has to? Otherwise you could'nt move a shape (with connectors) with the mouse

Regards Mario

 

[Domenic]

Hi Domenic ,

very good job; you are my hero! Thanks a lot!

You're very welcome! Glad I could help!

Isn't there any relation between the connector and the connected object? I guess it has to? Otherwise you could'nt move a shape (with connectors) with the mouse

The Connector property tests whether the specified shape is a connector...

shp.Connector

The BeginConnected property and EndConnected property test whether the beginning and end of the specified connector is connected to a shape...

shp.ConnectorFormat.BeginConnected

and

shp.ConnectorFormat.EndConnected

The BeginConnectedShape property and EndConnectedShape property return a Shape object that represents the shape attached to the beginning and end of a connector...

shp.ConnectorFormat.BeginConnectedShape

and

shp.ConnectorFormat.EndConnectedShape

And, of course, if you want the names of the shapes connected to the connector...

shp.ConnectorFormat.BeginConnectedShape.Name

and

shp.ConnectorFormat.EndConnectedShape.Name

 

[Keebellah]

I know this a very old post (like myself ) But the information here really helped me today, 3 years later

 

[Domenic]

I know this a very old post (like myself ) But the information here really helped me today, 3 years later

That's great, it's nice to know...

Cheers!

 

[Keebellah]

I just appended the snippets and did some editing and more and the results helped. I needed to ID over 9000 shapes !!! in a worksheet with half of those connectors leading from one to another and now I can 'track' each connector and locate the shape or group if in a group
?