Setting Visio Shape Cell Values By Connections

A recent newsgroup poster asked me to explain how to set the line color and weight of a connector according to the shapes it is connected to.  So, in this article, I have tried to explain one method of achieving this with minimal external coding.

In this example, I have created a rectangle shape that has a single Shape Data row, MyData, which has a fixed list of values, A;B;C.

The connector shape has been modified to trigger an event whenever a connection is made or unmade.  If the user successfully connects two rectangles with the same MyData value, then the line (weight and color) of the connector shape is amended to match the rectangle shapes.

The diagram below attempts to explain the relationships between the cells in the rectangle, connector and page.

The red arrows indicate the values updated by the VBA code; the green arrows indicate the values referenced from the page shapesheet, and the blue arrows indicate the values referenced within the same shapesheet.

Page

I modified the page shapesheet to have three new User-defined rows in order to have a centralised list of possible data values, and the corresponding line weights and colors for each value.  This is done with a simple semi-colon separated list, which could have been updated from an external data source.

User.MyDataList=”A;B;C”

User.MyLineWeights=”1pt;2pt;3pt”

User.MyLineColors=”RGB(255,0,0);RGB(0,255,0);RGB(0,0,255)”

In fact, these page cells could be inserted into the Master page of both the Rectangle and Connector shape (as in the sample diagram available below) and the act of dropping either master onto a new page will have the effect of duplicating these cells in the page shapesheet.

Rectangle

Then I modified a rectangle to have a new Shape Data row (Prop.MyData), where the format cell referenced the page User.MyDataList cell.

Prop.MyData.Format=ThePage!User.MyDataList

The line weight and color of the rectangle is changed with the following formulae:

LineWeight=GUARD(INDEX(LOOKUP(Prop.MyData,Prop.MyData.Format),ThePage!User.MyLineWeights))

LineColor=GUARD(INDEX(LOOKUP(Prop.MyData,Prop.MyData.Format),ThePage!User.MyLineColors))

Dynamic Connector

Now, in order to get the connector shape to fire an event whenever the connector shape is connected or disconnected, it is necessary to call a bit of code, ConnectIT, whenever the value in the BegTrigger or EndTrigger changes.  These cells are automatically updated whenever a connect or disconnect is done.

User.ConnectITTrigger=DEPENDSON(BegTrigger,EndTrigger)+CALLTHIS(“ConnectIT”,””)

User.BegIdx=0

User.EndIdx=0

I amended the LineWeight and LineColor formulae to respond to values in the User.BegIdx and User.EndIdx cells.

LineWeight=THEMEGUARD(IF(AND(User.BegIdx=User.EndIdx,User.BegIdx>0,ISERRVALUE(BeginX)=FALSE,ISERRVALUE(EndX)=FALSE),INDEX(User.BegIdx,ThePage!User.MyLineWeights),IF(CELLISTHEMED(FALSE),THEME(“ConnectorWeight”),SETATREFEXPR(THEME(“ConnectorWeight”)))))

LineColor=THEMEGUARD(IF(AND(User.BegIdx=User.EndIdx,User.BegIdx>0,ISERRVALUE(BeginX)=FALSE,ISERRVALUE(EndX)=FALSE),INDEX(User.BegIdx,ThePage!User.MyLineColors),IF(CELLISTHEMED(FALSE),SETATREFEXPR(THEME(“ConnectorColor”)),SETATREFEXPR(0))))

The bold characters are those bits that I added into the existing LineWeight and LineColor formulae, in order to check that the index of the values of the rectangle shapes at either end match each other.  If they do, then the line is changed too.

The ISERRVALUE(BeginX)=FALSE and ISERRVALUE(EndX)=FALSE part trap the changes due to either rectangle from being deleted.

VBA Code

Whenever the code is called, it clears the values in the User.BegIdx and User.EndIdx cells, then sets a formula to return the index of the value in the connected rectangle shapes, if they exist.

The following subroutine was added to a new module in the VBA project of the document:

Public Sub ConnectIT(ByVal shp As Visio.Shape)

If Visio.Application.IsUndoingOrRedoing Then
Exit Sub
End If
Dim cnx As Visio.Connect
'Ammended for Visio 2010 compatability
Dim beginFormulaSet As Boolean
Dim endFormulaSet As Boolean
Dim formula As String
Dim formulaWithName As String

For Each cnx In shp.Connects
    If cnx.ToSheet.CellExists("Prop.MyData", Visio.visExistsAnywhere) Then
        formula = "LOOKUP(Sheet." & cnx.ToSheet.ID & _
             "!Prop.MyData,ThePage!User.MyDataList)"
         formulaWithName = "LOOKUP(" & cnx.ToSheet.Name & _
             "!Prop.MyData,ThePage!User.MyDataList)"
         If cnx.FromCell.Name = "BeginX" Then
            If Not LCase(shp.Cells("User.BegIdx").formula) = _
                    LCase(formula) _
                And Not LCase(shp.Cells("User.BegIdx").formula) = _
                    LCase(formulaWithName) Then
                    shp.Cells("User.BegIdx").formula = "=" & formula
                End If
                beginFormulaSet = True
            ElseIf cnx.FromCell.Name = "EndX" Then
                If Not LCase(shp.Cells("User.EndIdx").formula) = _
                    LCase(formula) _
                    And Not LCase(shp.Cells("User.EndIdx").formula) = _
                    LCase(formulaWithName) Then
                    shp.Cells("User.EndIdx").formula = "=" & formula
                End If
                endFormulaSet = True
           End If
    End If
Next
If beginFormulaSet = False _
    And Not shp.Cells("User.BegIdx").formula = "0" Then
    shp.Cells("User.BegIdx").formula = "0"
End If
If endFormulaSet = False _
    And Not shp.Cells("User.EndIdx").formula = "0" Then
    shp.Cells("User.EndIdx").formula = "0"
End If

End Sub

Consequently, the connector shape responds to connections, disconnections and data changes in the connected rectangle shapes.

You can download the sample drawing here: http://sdrv.ms/OOIk2x or http://sdrv.ms/OOIpmW