Using Visio Color by Value on Connectors

Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is possible … with a little preparation!

Firstly, you need data on your connectors … now that can be added manually, but more likely, you want to add the data from a data source like an Excel table. The good news is that you use Data | External Data | Custom Import … but you will want to have a unique identifier for each connector between shapes, unless you want to drag and drop each row from the External Data window on to each line … one by one. The smarter way is to create a Shape Data row value with unique id for each connector, and use this to import the data automatically.

The Dynamic connector master shape is the default connector in most Visio templates, and some of them already include a customised version of it. If you start a new blank diagram, then the Connector tool will automatically create the Dynamic connector master in the Document Stencil … and tick the Match master by name on drop option. Simply use Edit Master | Edit Master Shape from the right-mouse context menu of the master in the Document Stencil. Then add something like the FromID, ToID and ID Shape Data rows, as in the following screenshot.

The FromID and ToID rows could be Invisible=True, and the Prop.ID.Value is set to the following formula:

=GUARD(Prop.FromID&”>”&Prop.ToID)

This Prop.ID.Value is the unique ID of the connector shape, and will be automatically created from the Prop.FromID and Prop.ToID values. Unfortunately there is no ShapeSheet formula to automatically get the value of a Shape Data row in the shapes that the connector is glued to at either end, so you will need a little code to update them. In this example, the Process shapes at either end of the connectors shapes have a Prop.ProcessNumber value, as shown by the numbers in the green Data Graphic Text callouts in the first screenshot. So, all that is needed is to transfer these values to the Prop.FromID and Prop.ToID Shape Data rows of each Dynamic connector shape. I wrote a little VBA to do this, but it could be in any suitable language.

I like to create some global constants to specify the names of the 1D connector master, the source Shape Data row of the 2D shapes, and the FromID and ToID Shape Data rows on the connector shapes.

Public Const MASTER_CONNECTOR As String = “Dynamic connector”
Public Const SHAPEDATA_SOURCEID As String = “ProcessNumber”
Public Const SHAPEDATA_FROMID As String = “FromID”
Public Const SHAPEDATA_TOID As String = “ToID”

Then you need a function to get the value of the source Shape Data row or an empty string if nothing found.

'Get the value of the named Shape Data row on a specified shape
'If numeric, then format it according to the format expression
Private Function GetIDFromShape( _
    ByVal shp As Visio.Shape, _
    ByVal propName As String) As String
On Error GoTo errHandler
    GetIDFromShape = ""
    If shp Is Nothing Then
        GoTo exitHere
    End If
    If shp.CellExistsU("Prop." & propName, _
            Visio.VisExistsFlags.visExistsAnywhere) Then
        If shp.CellsU("Prop." & propName & ".Type").ResultIU = 2 Then
            GetIDFromShape = format(shp.CellsU("Prop." & propName).ResultStrU(""), _
                shp.CellsU("Prop." & propName & ".Format").ResultStrU(""))
        Else
            GetIDFromShape = shp.CellsU("Prop." & propName).ResultStrU("")
        End If
    End If
        
exitHere:
    Exit Function
errHandler:
    MsgBox Err.description, vbExclamation, "GetIDFromShape"
    Resume exitHere
    Resume
End Function

Now, you are ready to loop through every connector shape on the active page to update the FromID and ToID values.

'Update every Dynamic connector on the active page with
'the specific Shape Data row values of each glued shape
'Clear the value if not glued
Public Sub UpdateDynamicConnectors()
On Error GoTo errHandler

Dim shpConnector As Visio.Shape
Dim mstConnector As Visio.Master
Dim selConnectors As Visio.Selection
Dim pag As Visio.Page
Dim cnxn As Connect
Dim fromID As String
Dim toID As String

    If MasterExists(MASTER_CONNECTOR) Then
        Set mstConnector = ActiveDocument.Masters(MASTER_CONNECTOR)
    Else
        GoTo exitHere
    End If
    
    Set selConnectors = ActivePage.CreateSelection(visSelTypeByMaster, 0, mstConnector)
    For Each shpConnector In selConnectors
        fromID = ""
        toID = ""
        For Each cnxn In shpConnector.Connects
            If cnxn.FromCell.Name = "BeginX" Then
                fromID = GetIDFromShape(cnxn.ToSheet, SHAPEDATA_SOURCEID)
            ElseIf cnxn.FromCell.Name = "EndX" Then
                toID = GetIDFromShape(cnxn.ToSheet, SHAPEDATA_SOURCEID)
            End If
        Next cnxn
        If shpConnector.CellExistsU("Prop." & SHAPEDATA_FROMID, _
                Visio.VisExistsFlags.visExistsAnywhere) <> 0 Then
            shpConnector.CellsU("Prop." & SHAPEDATA_FROMID).FormulaForceU = _
                "=""" & fromID & """"
        End If
        If shpConnector.CellExistsU("Prop." & SHAPEDATA_TOID, _
                Visio.VisExistsFlags.visExistsAnywhere) <> 0 Then
            shpConnector.CellsU("Prop." & SHAPEDATA_TOID).FormulaForceU = _
                "=""" & toID & """"
        End If

    Next shpConnector
    
exitHere:
    Exit Sub
errHandler:
    MsgBox Err.description, vbExclamation, _
        "UpdateDynamicConnectors"
    Resume exitHere
End Sub

You may already have data for each connector, but you can also create a basic table that contains each connection using a Shape Report, and then maybe use my free Shape Report Converter to turn the export into a proper table, suitable for enhancing with more data.

The next task is to use Data | External Data | Custom Import to link a table to Visio, and link to the connector shapes, as shown below.

Now, you will find that Visio may automatically create some Data Graphics when it adds Shape Data to each connector, but you can configure them to suit your circumstance.

So, what has happened? Visio automatically added some User-Defined Cells and Shape Data rows to each data-linked connector. It creates a ShapeSheet formula in the User.visDGCBVFill cell to evaluate the values in the Color By Value settings, and then the LineColor cell gets the resultant colour value from this cell. Note that the contents of the cells that have values just for the instance of the master shape are displayed in blue automatically.

As you can see, Color By Value is not the only Data Graphic that can be applied to connector lines … you can also use the Data Bars, Icon Sets and Text callouts too….

So, as you can see, Data Graphics can be applied to connectors too!

A word of caution for Data Visualizer users: You cannot use the data linking technique explained in this article to do add more links to the connectors under the control of Data Visualizer. However, there is a work around, which I will explain in a future article.

Related articles

When is a Visio Callout not a Callout?

I have been a Visio user/developer since the mid-1990’s and seen the word “callout” used as part of the name of many master shapes in Visio. The images below show five ways that the term “callout” has been applied to the name of Visio master shapes. Generally, each evolution has been an advance on the…

Using Notepad++ to Edit Visio ShapeSheet formulas

Visio shapes get their smartness from the formulas that are entered into the ShapeSheet, but editing these formulas can be extremely tricky and prone to error because of the lack of a modern programmer’s interface. Formulas can be quite long (up to 64k characters) but even medium size ones like the one in User.GetWorkdays cell…

Adding a second Function header bar to Visio swimlanes

I was recently asked if a second function header bar can be added to the swimlanes in the cross-functional flowchart templates in Visio. Some swimlanes can get quite wide, so it can be useful to have a duplicate function header shape on the far-side too. It is quite simple to duplicate the existing function header…

More Parsing XML Data in Visio Shapes

My last article looked at parsing an XML string with a known structure and order of elements and attributes. This can be acceptable in some scenarios, but what if the elements or attributes are in a different order? Then it is necessary to use the element and attribute names rather than their index position. This…

Parsing XML data in Visio Shapes

One of my current projects uses XML data, and some of the values in the XML data control the display and content of Visio shapes. Therefore, I looked deeper into how the XML data can be used directly to control parts of the graphics. Although the external data linking feature in Visio Professional and Visio…

Highlighting dirty text in Visio shapes

I suppose I should explain what I mean by dirty text first 🙂 I often display the value of a Shape Data row in Visio text, but sometimes the solution requires that the value is also editable. This is ok if the client accepts that all editing is done with the Shape Data window or…