I'm working in Excel 2013 to (programmatically) add a straight line connector between the lower right hand corner of a rectangle that is part of a grouped shape with the endpoint of a grouped series of line segments. As it stands, I can't even seem to do this manually on the Excel worksheet that contains these shapes.
- Only midpoints on the desired rectangle will accept the connector.
- The grouped series of line segments don't even show a "connection point" for the terminating end of the straight line connector.
Here's a graphic of what I'm trying to do:
[I don't have 10 "reputation points" so I can't seem to post a picture of what I'm trying to do. Not an especially helpful feature! How do I get reputation points in this game?]
I've been able to create and name the two groups and thought it would be a cinch to work with them to add a connector, but that has not been the case.
Here's the code I've been working with:
Sub create_new_profile() Dim firstRect As Shape Dim firstLine As Shape Set myDocument = Worksheets(1) Set s = myDocument.Shapes ' Set firstRect = s.Range("shpNewGarage") ' Set firstLine = s.Range("shpProfile") Dim Shp As Shape ' For Each Shp In myDocument.Shapes For Each Shp In s If Shp.Name = "shpNewGarage" Then Set firstRect = Shp Else End If Next Shp ' For Each Shp In myDocument.Shapes For Each Shp In s If Shp.Name = "shpProfile" Then Set firstLine = Shp Else End If Next Shp firstRect.Select 'this works firstLine.Select 'this works ' Set firstRect = s.AddShape(msoShapeRectangle, 100, 50, 200, 100) ' Set firstLine = s.AddShape(msoShapeRectangle, 300, 300, 200, 100) ' Set firstRect = ActiveSheet.Shapes.Range("shpNewGarage") ' Set firstLine = ActiveSheet.Shapes.Range("shpProfile") Dim c As Shape Set c = s.AddConnector(msoConnectorStraight, 0, 0, 100, 100) ' On Error Resume Next With c.ConnectorFormat **.BeginConnect ConnectedShape:=firstRect, ConnectionSite:=1** .EndConnect ConnectedShape:=firstLine, ConnectionSite:=1 ' .BeginConnect ConnectedShape:="shpNewGarage", ConnectionSite:=1 ' .EndConnect ConnectedShape:="shpProfile", ConnectionSite:=1 ' .BeginConnect ConnectedShape:=ActiveSheet.Shapes.Range("shpNewGarage"), ConnectionSite:=1 ' .EndConnect ConnectedShape:=ActiveSheet.Shapes.Range("shpProfile"), ConnectionSite:=1 c.RerouteConnections End With End Sub
This particular version of the code ends with a runtime error on the line immediately following the line:
Here's the error message:
[I don't have 10 "reputation points" so I can't seem to post a picture of the error message I'm getting. Again, how do I get reputation points?]
Any direction at all to help me accomplish this task programmatically would be greatly appreciated.
Thanks for explaining that I can now post images. That should help.
Here are the figures I'm working with:
The rectangle group (firstRect, "shpNewGarage") represents a new garage I plan to build between the existing one and the street. The profile group (firstLine, "shpProfile") represents the profile (side view/elevation) of the existing driveway (the light blue line.) The idea is to attach the new profile (red line) to the lower right corner of the new garage at one end and to the right end of the existing profile (curb) so that as I move the new garage up, down, right and left, the connector representing the new profile will remain attached to these points to show graphically the angle (grade) and length of the new driveway.
Here's the error message I receive when I run the code:
This looks like quite a hill to climb, as I am having problems even adding the connector to the desired points manually.
Thanks to all who have read/responded to my issue. Stackoverflow has been a great resource to me in the past, and this is the first time I've ever had to post my own fairly specific problem.