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.

image

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

image

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

image

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

Posted in Visio. 4 Comments »

4 Responses to “Setting Visio Shape Cell Values By Connections”

  1. Jarrod Says:

    I keep getting a bizarre behaviour.

    Sometimes, the whole thing will go nuts, and run the trigger over and over again; forever.

    Oddly, it will run the function against the connector I just (dis)connected, then against a random other one (but it thinks it is not connected to anything), then against the selected connector forever.

    (I added a MsgBox call at the start of the function)

    The only way out is to go into task manager and kill Visio 2010.

    Any ideas?

    • davidjpp Says:

      Yipes, it does on my Visio 2010 laptop too!
      I’m just going to check in Visio 2007 ….

      • davidjpp Says:

        Well, It works OK in Visio 2007 … now I’ll try to work out why it goes crazy in Visio 2010.

      • davidjpp Says:

        Thanks Jarrod for pointing this out … I have now updated the blog article and the downloads with revised code.
        It appears that Visio 2010 fires events in a different order, so I have refined the criteria for updating the cell formulas.
        Basically, I was able to get away with less perfect code before Visio 2010!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Chris Webb's BI Blog

Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

davecra.wordpress.com/

Microsoft Office 365 Development, and more...

PowerShell.Amsterdam

Automate, Accelerate, Accurate

johnvisiomvp

Life with Visio and other Microsoft Toys!

Title (Required)

Windows Server Essentials Tips & Tricks

Nilsandrey's Weblog

Just another WordPress.com weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Shapes, Stencils, Drawings Templates, Tutorials, Tips & Developer Info for Microsoft Visio

Hannes's Virtual Earth

Tips & Tricks around Mapping and Cloud Computing

Pluralsight blog

be smart, be clear, be visual ...

%d bloggers like this: