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

mario1968

New Member
Joined
Feb 27, 2017
Messages
4
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.

Code:
[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!
 
Upvote 0
Di Domenic,

looks very good.

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

Code:
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
 
Upvote 0
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...

Code:
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...

Code:
    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?
 
Upvote 0
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
 
Upvote 0
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...

Code:
shp.Connector

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

Code:
shp.ConnectorFormat.BeginConnected

and

Code:
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...

Code:
shp.ConnectorFormat.BeginConnectedShape

and

Code:
shp.ConnectorFormat.EndConnectedShape

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

Code:
shp.ConnectorFormat.BeginConnectedShape.Name

and

Code:
shp.ConnectorFormat.EndConnectedShape.Name
 
Upvote 0
I know this a very old post (like myself ;)) But the information here really helped me today, 3 years later :)
 
Upvote 0
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
(y)?:cool:
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top