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)



It is impossible to write:

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

or even:


[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.


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


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


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






Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

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

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another 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

Smart graphics for visual people

%d bloggers like this: