Controlling Visio layers with linked data

Readers of my blog will know that I use the layers in Visio pages to change the display for different scenarios. My macro to toggle layers on/off has been very popular, and I have written an add-in to manage layers that is widely used. However, I was recently asked if the layer settings can be controlled from linked data. Linking data in Visio has been available in all desktop editions, except Standard, since 2007, and I have written many articles and two books extolling this feature, but I haven’t so far controlled layer settings with it, so this article describes how this can be done … as shown in the following screen recording!

The first problem is that the layer settings are stored in the page, and you cannot link the page to data, so data-linked shapes in the page are required to push the settings into the page.

The second problem is that layers are known by name, but they are stored with a unique index number in the page. However this number can be different for the same layer name in different pages! That means that a linked data recordset cannot include the layer index since it is not a constant. Therefore, the key column has to be the layer name.
The third problem is that deleted layers are not really deleted …. their name in the page is just blanked out! If the same layer name is added as a previously deleted one, then it is assigned the same index number.

The fourth problem is that this layer number cannot be assigned to a variable in a ShapeSheet formula and referenced directly. This means that, although I can write a formulas like this:

User.LayerIdx =3
User.VisibleTrigger =SETF(GetRef(ThePage!Layers.Visible[3]),Prop.Visible)
Prop.Layer=GUARD(ThePage!Layers.Name[3])
Prop.Visible=True

It is impossible to write:

User.VisibleTrigger =SETF(GetRef(ThePage!Layers.Visible[User.LayerIdx]),Prop.Visible)

or even:

User.VisibleTrigger=SETF("ThePage!Layers.Visible["&INT(User.LayerIdx)&"]",Prop.Visible)

[This is how we used to write these formulas before GetRef(…) was added to Visio]
However, none of the above formulas respond to the Visible value being changed! So, a different approach would need to be taken. This is where the SETATREF(…) function is needed because it redirects value changes from the UI.

Prop.Visible=SETATREF(ThePage!Layers.Visible[3],1,0)

This works, but again, the layer index cannot be another cell reference :

Prop.Visible=SETATREF(ThePage!Layers.Visible[INT(User.LayerIdx)],1,0)

This will just not work, so a little cunning is required …. why not set the formula when the value of User.LayerIdx is changed?

User.VisibleTrigger=SETF(GetRef(Prop.Visible),"=SETATREF(ThePage!Layers.Visible["&INT(User.LayerIdx)&"],1,0)")

The same technique can be used to set the formulas in Prop.Layer and other cells, thus enabling the setting of all of the layer property values.
All that is need now is to be able to add a shape per layer, link each one to a row in a data source using the name as the primary key! This means that the User.LayerIdx values need to be set as easily as possible, and the other values will follow suit.
Fortunately there is a feature in Visio that can do this! A shape can be a member of a list container, and it can get an position number in the list using the ListOrder() function. So, if the User.LayerIdx formula is set to this. It will have a value of -1 if it is not in a list, else it will be an unique number, starting from 1.
I will not be going into the abilities of list containers just now, because I have done several posts about them previously (just enter List in the Search box), but this short screen recording shows how a list container shape can be added to a page, and automatically insert a number of other shapes into it. In this case, the LinkedLayerList shapes will automatically add six LinkedLayer shapes into itself. The ListOrder() function in the User.LayerIdx cell of each shape gets a unique number (1 to 6 in this case), and so all of the Shape Data rows get updated automatically. It only looks like there are four shapes because those without a Prop.Layer value are automatically made zero high. Thus, the bottom shape, labelled Container actually has a User.LayerIdx of 6, as can be seen from the PageSheet.

All that remains is to link the LinkedLayer shapes to the rows in a suitable External Data Recordset, as can be seen at the start of this article.

Here is a link to the Visio document : ControlLayersWithLinkedData.vsdx