searching for shapes

[#DIV/0]

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 ?

 

[Peter T]

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]

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.TextFrame.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)

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

 

[Peter T]

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

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.TextFrame.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.... :-(

 

[#DIV/0]

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).ConnectorFormat.BeginConnectedShape.TextFrame.Characters.Text
ToShape =
ActiveSheet.Shapes(Application.Caller).ConnectorFormat.EndConnectedShape.TextFrame.Characters.Text
With Connect
.FromText.Caption = FromShape
.ToText.Caption = ToShape
.Show
End With
End Sub

 

[Peter T]

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]

'Connect' is just the name of the UserForm.
What I'm doing is a many-to-many diagram of a complex quality system,
charting the cross-references between procedures, instructions and forms and
the departments. If it was a regular flow chart I could probably use
"parent" and "child" but I'm not dealing with linear links and one shape can
have any number of 'parents' or 'children'.
You say "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" and that's exactly what I want to do.
I really want to click on the shapes, not the connectors, and get the text
from any connected shapes as strings which I'll put into labels on my
UserForm.
So my macro has to:
1. look for a connectors
2. get the text from the shape at the other end (this is the part I've
already figured)
3. loop until there are no more connectors and the names are all 'saved' as
strings (shape1, shape2 etc)

Part 3 I think I can wrestle with on my own, part 1 is the killer. I can't
see how to tell the shape I click that it must look for a connector.

 

[Peter T]

Sounds like an interesting use of shapes.
OK, I get you want to select all shapes connected to the currently selected
shape(?), but I don't get what you are doing with textbox text stuff at all.

FWIW -

Part 3 I think I can wrestle with on my own, part 1 is the killer.

I'd say other way round, the first part is easy, it's the middle bit of
'sorting' it all out that's difficult!

FYI, I have the following -
Main routine returns an array of arrays, where each array contains the index
numbers of 'sets' of connected shapes.
The of arrays can be used for various id type purposes, one of which (for
your needs I think) -

Search the arrays for the index that matches the currently selected object
Select all objects id'd by the index numbers in the matched array. IOW
expand the selection to include all shapes connected to it.

Although I could post the code, it's quite long and to make any sense of it
requires the connected shapes. If interested can send wb with 200+ shapes
with lots of sets of connections. (address below, insert the missing
punctuation).

Regards,
Peter T
pmbthornton gmail com

 

[#DIV/0]

Peter,
Thanks for the code. I'm stunned by the complexity of what was needed and by
the time you must have put into your solution.
I think it'll take my an hour or two to adapt it to my project and then
probably several weeks to understand what you did!
Because in the end that's the value of this community - learning, not just
getting a solution served up from someone else (which is what I've got so far
so if I don't learn from it I deserve to be shot).

Thanks again.