Hi,
1. How can I fill a listbox with the names of all the shapes in a worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select shape1 can
I get a list of the connected shapes ? Verifying the presence of connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)

Re Q1, this functionality is built in.

Customize toolbars
Commands, Drawing, Select Multiple Objects
drag onto your Drawing toolbar

(to list programmatically see following)

Re Q2, you need to look for connectors and see if 'both ends' are connected.
This should get you started -

Sub ListConnections()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Connector Then
With shp.ConnectorFormat
If .BeginConnected And .EndConnected Then ' only if both
ends ?
Debug.Print shp.Name, .BeginConnectedShape.Name, .EndConnectedShape.Name
End If
End With
Else
Debug.Print shp.Name
End If
Next

End Sub

How to turn the results into something useful depends on what you want to do
and could get a bit fiddly.

Regards,
Peter T

"#DIV/0" wrote in message
...
Hi,
1. How can I fill a listbox with the names of all the shapes in a
worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select shape1
can
I get a list of the connected shapes ? Verifying the presence of
connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)

Hi Peter,
I know it's going to get *very* fiddly *very* quickly - I have over 200
shapes on this sheet!
I've made a start with what you gave me, just testing stuff out. For a known
connector name *this* works:

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.BeginConnectedShape.TextFram e.Characters.Text
ToShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.EndConnectedShape.TextFrame. Characters.Text
MsgBox FromShape & " --- " & ToShape
End Sub

I can't get it to work based on a selected connector. Is there something
like "ActiveShape" to use for referencing ?

I know I should be using Visio for this project.... :-(
--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

Re Q1, this functionality is built in.

Customize toolbars
Commands, Drawing, Select Multiple Objects
drag onto your Drawing toolbar

(to list programmatically see following)

Re Q2, you need to look for connectors and see if 'both ends' are connected.
This should get you started -

Sub ListConnections()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Connector Then
With shp.ConnectorFormat
If .BeginConnected And .EndConnected Then ' only if both
ends ?
Debug.Print shp.Name, .BeginConnectedShape.Name, .EndConnectedShape.Name
End If
End With
Else
Debug.Print shp.Name
End If
Next

End Sub

How to turn the results into something useful depends on what you want to do
and could get a bit fiddly.

Regards,
Peter T

"#DIV/0" wrote in message
...
Hi,
1. How can I fill a listbox with the names of all the shapes in a
worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select shape1
can
I get a list of the connected shapes ? Verifying the presence of
connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)

I can't get it to work based on a selected connector. Is there something
like "ActiveShape" to use for referencing ?

dim shp as shape
on error resume next
set shp = nothing ' clear if possibly previously assigned
set shp = activesheet.shapes(selection.name)
on error goto 0
if not shp is nothing then etc

There's a very slight risk of grabbing the wrong shape, can happen when
copied shapes end up with duplicate names, alternatively

dim shr as shaperange
on error resume next
set shr = nothing
set shr = selection.shaperange
on error goto 0

That'd also grab a single or multiple selected shapes.

If I follow, you want to select or reference all shapes that might be
'connected' to a single selected shape; should be possible (if that's what
you want to do).

I know it's going to get *very* fiddly *very* quickly - I have over 200

Yes somewhat, it's more a question of getting the old head round how to go
about it, but no difference if there's 10 or 10,000 shapes to deal with.

Regards,
Peter T



"#DIV/0" wrote in message
...
Hi Peter,
I know it's going to get *very* fiddly *very* quickly - I have over 200
shapes on this sheet!
I've made a start with what you gave me, just testing stuff out. For a
known
connector name *this* works:

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.BeginConnectedShape.TextFram e.Characters.Text
ToShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.EndConnectedShape.TextFrame. Characters.Text
MsgBox FromShape & " --- " & ToShape
End Sub

I can't get it to work based on a selected connector. Is there something
like "ActiveShape" to use for referencing ?

I know I should be using Visio for this project.... :-(
--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

Re Q1, this functionality is built in.

Customize toolbars
Commands, Drawing, Select Multiple Objects
drag onto your Drawing toolbar

(to list programmatically see following)

Re Q2, you need to look for connectors and see if 'both ends' are
connected.
This should get you started -

Sub ListConnections()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Connector Then
With shp.ConnectorFormat
If .BeginConnected And .EndConnected Then ' only if both
ends ?
Debug.Print shp.Name, .BeginConnectedShape.Name, .EndConnectedShape.Name
End If
End With
Else
Debug.Print shp.Name
End If
Next

End Sub

How to turn the results into something useful depends on what you want
to do
and could get a bit fiddly.

Regards,
Peter T

"#DIV/0" wrote in message
...
Hi,
1. How can I fill a listbox with the names of all the shapes in a
worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select
shape1
can
I get a list of the connected shapes ? Verifying the presence of
connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)

Good morning Peter,
I'll look at that. In the meatime this is where I've got to on my own. I
discovered how to use application.caller so instead of selecting a shape then
running a macro I've assigned this macro to all the connectors in advance.

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape =
ActiveSheet.Shapes(Application.Caller).ConnectorFo rmat.BeginConnectedShape.TextFrame.Characters.Text
ToShape =
ActiveSheet.Shapes(Application.Caller).ConnectorFo rmat.EndConnectedShape.TextFrame.Characters.Text
With Connect
.FromText.Caption = FromShape
.ToText.Caption = ToShape
.Show
End With
End Sub

--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

I can't get it to work based on a selected connector. Is there something
like "ActiveShape" to use for referencing ?

dim shp as shape
on error resume next
set shp = nothing ' clear if possibly previously assigned
set shp = activesheet.shapes(selection.name)
on error goto 0
if not shp is nothing then etc

There's a very slight risk of grabbing the wrong shape, can happen when
copied shapes end up with duplicate names, alternatively

dim shr as shaperange
on error resume next
set shr = nothing
set shr = selection.shaperange
on error goto 0

That'd also grab a single or multiple selected shapes.

If I follow, you want to select or reference all shapes that might be
'connected' to a single selected shape; should be possible (if that's what
you want to do).

I know it's going to get *very* fiddly *very* quickly - I have over 200

Yes somewhat, it's more a question of getting the old head round how to go
about it, but no difference if there's 10 or 10,000 shapes to deal with.

Regards,
Peter T



"#DIV/0" wrote in message
...
Hi Peter,
I know it's going to get *very* fiddly *very* quickly - I have over 200
shapes on this sheet!
I've made a start with what you gave me, just testing stuff out. For a
known
connector name *this* works:

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.BeginConnectedShape.TextFram e.Characters.Text
ToShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.EndConnectedShape.TextFrame. Characters.Text
MsgBox FromShape & " --- " & ToShape
End Sub

I can't get it to work based on a selected connector. Is there something
like "ActiveShape" to use for referencing ?

I know I should be using Visio for this project.... :-(
--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

Re Q1, this functionality is built in.

Customize toolbars
Commands, Drawing, Select Multiple Objects
drag onto your Drawing toolbar

(to list programmatically see following)

Re Q2, you need to look for connectors and see if 'both ends' are
connected.
This should get you started -

Sub ListConnections()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Connector Then
With shp.ConnectorFormat
If .BeginConnected And .EndConnected Then ' only if both
ends ?
Debug.Print shp.Name, .BeginConnectedShape.Name, .EndConnectedShape.Name
End If
End With
Else
Debug.Print shp.Name
End If
Next

End Sub

How to turn the results into something useful depends on what you want
to do
and could get a bit fiddly.

Regards,
Peter T

"#DIV/0" wrote in message
...
Hi,
1. How can I fill a listbox with the names of all the shapes in a
worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select
shape1
can
I get a list of the connected shapes ? Verifying the presence of
connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)

Yes Application.Caller will return the name of the object to which the macro
is assigned. Not sure what the 'Connect' in your 'With Connect' is supposed
to refer to, as written not to anything.

Not sure of your overall objective but it'd be perfectly feasible for the
macro to go on and select all the objects that are mutually 'connected' to
the connecter that called the macro, or similarly select all objects
'connected' to any given object on the sheet, eg the currently selected
object. Virtually the same thing, return a list of all 'sets' of connected
objects on the sheet.

Regards,
Peter T

"#DIV/0" wrote in message
...
Good morning Peter,
I'll look at that. In the meatime this is where I've got to on my own. I
discovered how to use application.caller so instead of selecting a shape
then
running a macro I've assigned this macro to all the connectors in advance.

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape =

ActiveSheet.Shapes(Application.Caller).ConnectorFo rmat.BeginConnectedShape.T
extFrame.Characters.Text
ToShape =

ActiveSheet.Shapes(Application.Caller).ConnectorFo rmat.EndConnectedShape.Tex
tFrame.Characters.Text
With Connect
.FromText.Caption = FromShape
.ToText.Caption = ToShape
.Show
End With
End Sub

--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

I can't get it to work based on a selected connector. Is there
something
like "ActiveShape" to use for referencing ?

dim shp as shape
on error resume next
set shp = nothing ' clear if possibly previously assigned
set shp = activesheet.shapes(selection.name)
on error goto 0
if not shp is nothing then etc

There's a very slight risk of grabbing the wrong shape, can happen when
copied shapes end up with duplicate names, alternatively

dim shr as shaperange
on error resume next
set shr = nothing
set shr = selection.shaperange
on error goto 0

That'd also grab a single or multiple selected shapes.

If I follow, you want to select or reference all shapes that might be
'connected' to a single selected shape; should be possible (if that's
what
you want to do).

I know it's going to get *very* fiddly *very* quickly - I have over
200

Yes somewhat, it's more a question of getting the old head round how to
go
about it, but no difference if there's 10 or 10,000 shapes to deal with.

Regards,
Peter T



"#DIV/0" wrote in message
...
Hi Peter,
I know it's going to get *very* fiddly *very* quickly - I have over
200
shapes on this sheet!
I've made a start with what you gave me, just testing stuff out. For a
known
connector name *this* works:

Sub WhatsConnected()
Dim FromShape As String
Dim ToShape As String
FromShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.BeginConnectedShape.TextFram e.Characters.Text
ToShape = ActiveSheet.Shapes("AutoShape
407").ConnectorFormat.EndConnectedShape.TextFrame. Characters.Text
MsgBox FromShape & " --- " & ToShape
End Sub

I can't get it to work based on a selected connector. Is there
something
like "ActiveShape" to use for referencing ?

I know I should be using Visio for this project.... :-(
--
David M
WinXP - Office2003 (Italian)


"Peter T" wrote:

Re Q1, this functionality is built in.

Customize toolbars
Commands, Drawing, Select Multiple Objects
drag onto your Drawing toolbar

(to list programmatically see following)

Re Q2, you need to look for connectors and see if 'both ends' are
connected.
This should get you started -

Sub ListConnections()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Connector Then
With shp.ConnectorFormat
If .BeginConnected And .EndConnected Then ' only if
both
ends ?
Debug.Print shp.Name, .BeginConnectedShape.Name,
..EndConnectedShape.Name
End If
End With
Else
Debug.Print shp.Name
End If
Next

End Sub

How to turn the results into something useful depends on what you
want
to do
and could get a bit fiddly.

Regards,
Peter T

"#DIV/0" wrote in message
...
Hi,
1. How can I fill a listbox with the names of all the shapes in a
worksheet ?
I've given them meaningful names so I can identify them.

2. From a selected shape is there a way to find out the names of
any
connected shapes ?
eg. shape1 is connected to shape2, shape3 and shape4. If I select
shape1
can
I get a list of the connected shapes ? Verifying the presence of
connectors
and seeing what's at the other end ?

--
David M
WinXP - Office2003 (Italian)