Can anyone help me figure out why this routine never finds the connectors
(there are at least two) on the sheet?
"r" is a range, by the way.

The loop never runs, apparently it doesn't find the type 2 shape
(ConnectorElbow):

For Each sh In ActiveSheet.Shapes

If sh.Type = 2 Then 'I think 2 is a MsoConnectorElbow shape
With sh.ConnectorFormat
Set endsh = .EndConnectedShape 'endsh is the name of the
shape that the line is connected to
Set begsh = .BeginConnectedShape 'begsh is the name of the
shape at the beginning of the line.
End With
Set isect1 = Application.Intersect(Range("r"), Range(endsh.Range))
Set isect2 = Application.Intersect(Range("r"), Range(begsh.Range))
If (isect1 Is Nothing) And (isect2 Is Nothing) Then 'this is not the
droid you are looking for
'do nothing, continue in the loop
Else 'found the connector, need to kill it.
sh.Delete
End If
End If
Next sh


Thanks!

Sub ABCEFG()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Connector Then
If sh.ConnectorFormat.Type = 2 Then 'I think 2 is a MsoConnectorElbow
shape
MsgBox sh.Name & "is an elbow"
End If
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

Can anyone help me figure out why this routine never finds the connectors
(there are at least two) on the sheet?
"r" is a range, by the way.

The loop never runs, apparently it doesn't find the type 2 shape
(ConnectorElbow):

For Each sh In ActiveSheet.Shapes

If sh.Type = 2 Then 'I think 2 is a MsoConnectorElbow shape
With sh.ConnectorFormat
Set endsh = .EndConnectedShape 'endsh is the name of the
shape that the line is connected to
Set begsh = .BeginConnectedShape 'begsh is the name of the
shape at the beginning of the line.
End With
Set isect1 = Application.Intersect(Range("r"), Range(endsh.Range))
Set isect2 = Application.Intersect(Range("r"), Range(begsh.Range))
If (isect1 Is Nothing) And (isect2 Is Nothing) Then 'this is not the
droid you are looking for
'do nothing, continue in the loop
Else 'found the connector, need to kill it.
sh.Delete
End If
End If
Next sh


Thanks!

Thanks Tom -
So I needed to determine that the shape was a connector first. I was
wondering how it knows that "type" = 2 isn't some OTHER thing with type = 2.

While I have you, in that same block of code I see that I have another
problem. I have two shape names identified (begsh and endsh), and I want to
see if either shape lies within a range of cells (r).
If i use "intersect(a, b)" then I need to know the location of that shape in
terms of cell location, right? The shape.left and shape.right will give me
it's location in points, not cells.

How would you figure out if this shape is within a certain range of cells?
Thanks -
dan


"Tom Ogilvy" wrote:

Sub ABCEFG()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Connector Then
If sh.ConnectorFormat.Type = 2 Then 'I think 2 is a MsoConnectorElbow
shape
MsgBox sh.Name & "is an elbow"
End If
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

Can anyone help me figure out why this routine never finds the connectors
(there are at least two) on the sheet?
"r" is a range, by the way.

The loop never runs, apparently it doesn't find the type 2 shape
(ConnectorElbow):

For Each sh In ActiveSheet.Shapes

If sh.Type = 2 Then 'I think 2 is a MsoConnectorElbow shape
With sh.ConnectorFormat
Set endsh = .EndConnectedShape 'endsh is the name of the
shape that the line is connected to
Set begsh = .BeginConnectedShape 'begsh is the name of the
shape at the beginning of the line.
End With
Set isect1 = Application.Intersect(Range("r"), Range(endsh.Range))
Set isect2 = Application.Intersect(Range("r"), Range(begsh.Range))
If (isect1 Is Nothing) And (isect2 Is Nothing) Then 'this is not the
droid you are looking for
'do nothing, continue in the loop
Else 'found the connector, need to kill it.
sh.Delete
End If
End If
Next sh


Thanks!

You use the TopLeftCell and BottomRightCell properties to find the cells
covered by the shape. (wholely or partially)

dim rngEnd as Range, rngBeg as Range
For Each sh In ActiveSheet.Shapes
If sh.Connector Then
If sh.ConnectorFormat.Type = 2 Then
With sh.ConnectorFormat
Set endsh = .EndConnectedShape 'endsh is the name of the
Set begsh = .BeginConnectedShape End With
set rngEnd = range(endsh.TopLeftCell,endsh.BottomRightCell)
set rngBeg = range(begsh.TopLeftCell,begsh.BottomRightCell)
Set isect1 = Application.Intersect(Range("r"), rngEnd)
Set isect2 = Application.Intersect(Range("r"), rngBeg)
If (isect1 Is Nothing) And (isect2 Is Nothing) Then

Else 'found the connector, need to kill it.
sh.Delete
End If
End If
End if
Next sh

--
Regards,
Tom Ogilvy


"Dan" wrote:

Thanks Tom -
So I needed to determine that the shape was a connector first. I was
wondering how it knows that "type" = 2 isn't some OTHER thing with type = 2.

While I have you, in that same block of code I see that I have another
problem. I have two shape names identified (begsh and endsh), and I want to
see if either shape lies within a range of cells (r).
If i use "intersect(a, b)" then I need to know the location of that shape in
terms of cell location, right? The shape.left and shape.right will give me
it's location in points, not cells.

How would you figure out if this shape is within a certain range of cells?
Thanks -
dan


"Tom Ogilvy" wrote:

Sub ABCEFG()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Connector Then
If sh.ConnectorFormat.Type = 2 Then 'I think 2 is a MsoConnectorElbow
shape
MsgBox sh.Name & "is an elbow"
End If
End If
Next sh
End Sub

--
Regards,
Tom Ogilvy


"Dan" wrote:

Can anyone help me figure out why this routine never finds the connectors
(there are at least two) on the sheet?
"r" is a range, by the way.

The loop never runs, apparently it doesn't find the type 2 shape
(ConnectorElbow):

For Each sh In ActiveSheet.Shapes

If sh.Type = 2 Then 'I think 2 is a MsoConnectorElbow shape
With sh.ConnectorFormat
Set endsh = .EndConnectedShape 'endsh is the name of the
shape that the line is connected to
Set begsh = .BeginConnectedShape 'begsh is the name of the
shape at the beginning of the line.
End With
Set isect1 = Application.Intersect(Range("r"), Range(endsh.Range))
Set isect2 = Application.Intersect(Range("r"), Range(begsh.Range))
If (isect1 Is Nothing) And (isect2 Is Nothing) Then 'this is not the
droid you are looking for
'do nothing, continue in the loop
Else 'found the connector, need to kill it.
sh.Delete
End If
End If
Next sh


Thanks!