Dynamically Referencing Other Shapes' Custom Properties/Shape Data

[jimm_i]

Hi guys,
I've tried searching the forums (and almost everywhere else online I could find) for this & haven't had much luck, so here goes...
I'll try and explain what i'm doing (trying to do??) before I get to my problem because i'm sure that will help with explaining my problem.

I've created a diagram for work that demonstrates the layout of computer assets in an installation (i.e. computers, monitors & cables). This installation is quite large, involving 9 different computers, 24 monitors & a whole mess of cables (which are just dynamic connectors). All of my shapes have custom properties that i've assigned, e.g.:
For my cables I have:
Type (DVI, VGA etc.)
Destination 1 (where one end of the cable is connected to)
Destination 2 (where the other end of the cable is connected to)
Length (how long a cable I need)

Now for my problem:
I've created a stencil that includes a heap of different masters with all the custom properties stored & information prefilled as far as possible (e.g. I have a DVI cable master, which has the Type prefilled).
What I want to happen is for my cables to inherit the Destination information from the shapes that I connect them to.
My thoughts so far are to use the shape text (TheText) of the shapes to store the names, then use shapetext to extract the info that I need.

The formula i've got so far is this:
Custom Property Value=GUARD(SHAPETEXT(Microphone.59!TheText))         (Microphone.59 is the shape that is connected to one end of this cable)
This formula will return the shapetext of Microphone.59, but its not too helpful for when I want to join the cable to a different shape.

My question is, is there some way of replacing the "Microphone.59" text with a reference to a different cell in the shapesheet. From what i've seen so far, the only cells that contain any reference to Microphone.59 are BeginX, BeginY (in 1-D Endpoints) & BegTrigger (In Glue Info). All my attempts so far to grab the text from these cells has only resulted in getting the result of the formula that they are a part of & not the actual text that I want.

As you can probably tell, i'm quite new to using shapesheet in visio & am struggling quite a bit with getting my head around the terminology & how it all works.

Anyway, any help would be very much appreciated

[vojo]

not without VBA.   I have long thought this was a shortcoming of visio....lots of applications could use this kind of thing (passing properties between shapes real time/dynamically).

The VBA would need to discover the connection between shapes, retrieve info from prop cells in "source" and place info in prop cells in "destination".  You could make a simplier VBA where user selects source shape first then ctrl shift select destnaton shape....then fire the code...would save on all the issues of shape discovery and connections.   The VBA would just "blindly" copy source data of interest into dest prop cells.

Also, I believe that the VBA can NOT be saved in the stencil (I never got a clear answer on how to do that).  So you would need a template for the applications so that the special VBA is present.

First glance...I dont think linking to an excell spreadsheet helps (unless its easier to export all shape data to excell, manually copy cells around, inport the results back into all the shapes).

Good luck

[Visio Guy]

I would say "Yup, you need code to get this done" as well. You can see that shape-to-shape intelligence works well, ONCE formulas are established. But there's no way to get them established without code. And I second the motion that this is a long-time weakness of Visio. We could do so much more with this kind of capability.

And some of it is coming in 2010. The set of objects to which you can refer in the ShapeSheet is expanding, but still not quite smart enough to get lots of information about neighbors, parent groups or connected-to shapes.

One last trick that I haven't fully exploited, but might be worth a look:

In the stencil: File > Shapes > Visio Extras > Callouts, there are three "Custom callouts" that are able to pick up information about the shapes to which they point. They call a Visio-supplied add-on that fills out information in user-cells of the callout when the user changes the callout-tail.

It is possible to figure out what this add-on does, and exploit some its operations. The danger is that Visio will discontinue the add-on. Such manipulation would probably be "officially unsupported" by Microsoft as well. But at least it will be fun trying!

[jimm_i]

Thanks guys,
I've done a bit of simple work in Excel with VBA, but nothing really else.
So far from what i've seen Visio is quite a bit different (or maybe I'm just not understanding it properly yet).

I did a bit more research on different possible VBA solutions for my problem & came up with a sequence that should extract the info I need if I can figure out how to use it. My main issue at the moment is not knowing how to create a function in VBA that I can just type into a cell in the Shapesheet (the same as you would do in Excel if its possible???).

Here is the code that i've come up with so far with an explanation of what i'm trying to accomplish with each line:

Function Connections()
    strBeginX = shpObj.Cells("BeginX").Formula                                                   Gets the formula from BeginX of my connector
    strBeginX = Right(strBeginX, Len(strBeginX) -                                             Strips off the stuff at the front of the formula string before my connected shape name
    strBeginX = Left(strBeginX, (Len(strBeginX) - 33) / 2)                                    Strips off the stuff at the end of the formula string after my connected shape name
    strBeginX = strBeginX + "!TheText"                                                              Appends "!TheText" to the end of my shape string
    Connections = Shape.ShapeText(strBeginX)                                                  Gives a return value for my function
End Function


So a couple more questions...
1. Am I barking up the wrong tree with this code?
2. If this code is close to something useful, how do I actually enter the function into my Shapesheet cell?

Cheers 

[jimm_i]

the should be 8 )

[aledlund]

note from the side: You cannot enter vba code into a shapesheet cell. That function was removed a couple of years ago because of security reasons.
al

[Visio Guy]

...thus killing the potential for some cool "Best one line of in-ShapeSheet VBA code" competitions

[jimm_i]

Finally got a working solution:

Sub Connections()
    Dim string1 As String
    Dim string2 As String
    Dim shpObj As Visio.Shape
    Dim temp As Integer
    Dim iShapeCount As Integer
    Dim i As Integer
    'Get the active shapes.
    Set pagObj = ActiveWindow.Selection
    'Total number of shapes.
    iShapeCount = pagObj.Count
   
    For i = 1 To iShapeCount
        'Set the active shape
        Set shpObj = pagObj.Item(i)
        'Clear the strings
        string1 = ""
        string2 = ""
        'Get the formula from the BegTrigger Cell of the current shape
        string1 = shpObj.CellsU("BegTrigger").Formula
        'Strip out the leading characters
        string2 = Right(string1, Len(string1) - 11)
        'Strip out the trailing characters
        string1 = Left(string2, Len(string2) - 11)
        'Assemble the required formula to put in the custom property cell
        string2 = "=ShapeText(" + string1 + "TheText)"
        'Insert the formula to the required cell
        shpObj.CellsU("Prop.Row_3").Formula = string2
       
        'Repeat for the other connection point
        string1 = ""
        string2 = ""
        string1 = shpObj.CellsU("EndTrigger").Formula
        string2 = Right(string1, Len(string1) - 11)
        string1 = Left(string2, Len(string2) - 11)
        string2 = "=ShapeText(" + string1 + "TheText)"
        shpObj.CellsU("Prop.Row_6").Formula = string2
    Next

End Sub

I just need to be careful with what shapes I am actually connected to, as i've made all my own master shapes that are made up of lots of sub-shapes, so if the connection point is attached to a sub shape rather than the main shape, the TheText bit won't grab the right shape text.

Thanks for your help guys

[Visio Guy]

Hey jimm_i,

If you are looking to trace connections to shapes (ie: find out what shape is on the other side of the connector), then the shp.FromConnects method will help.

This article will also give you some tips for tracing connections: Analyze Connectivity Between Process Flows