I was going to title this “When is a truth not a truth?” because I have found that Visio can sometimes refuse to apparently update the displayed value in a cell, even though it has been changed. However, this was, in this instance, down to a misunderstanding of the formula evaluation events on my part. Take the scenario where you have some data that you want to be available to all pages and all shapes in the Visio document. There are some built-in Document Properties in Visio that are accessible to all pages and shapes using ShapeSheet functions ( eg TITLE(), SUBJECT(), COMPANY(), Categories / CATEGORY(), Tags / KEYWORDS(), Comments / DESCRIPTION(), Author / CREATOR(), MANAGER() and HYPERLINKBASE()). However, Visio does not have custom Document Properties like the other Office applications. Instead, Visio allows for the addition of User-Defined Cells and Shape Data rows in the DocumentSheet of the document. These can be uniquely named, and Shape Data rows can be given a data type (of sorts), a label, prompt and format.
These values can be accessed via a ShapeSheet function from anywhere within the document, which ensures that you only have one version of the truth. However, there is no non-developer user-interface to edit these DocumentSheet Shape Data values in Visio, so what do you do? And how can you be sure that you are always seeing these centralised values correctly?
I created the following master shape to test how this can be done. It is a group shape that contains a single StartDate Shape Data row, which is used to edit or view the date in the similarly named Shape Data row in the DocumentSheet. For good measure, I also added a similarly named Shape Data row to the page, so the Shape Data window in the page could also be used. I then used Insert / Field to display the values in the ShapeSheet, PageSheet and DocumentSheet:
The easiest way to provide a user-interface to the Shape Data rows of the DocumentSheet is to create a shape that contains the same rows, but the Label, Prompt, Type, Format, etc gets the values from the same cells in the DocumentSheet by using the TheDoc! prefix, eg:
=TheDoc!Prop.ShapeData.Label
This is not suitable for the Value cell because the user needs to edit this in the UI, and all other references to the value need to get the latest value.
Date fails to display correctly
Initially, I merely used the SETATREF(…) function in the Value cell of the Prop.StartDate row of the master shape.
=SETATREF(TheDoc!Prop.StartDate)
This function will push the value to the target cell, in this case the Prop.StartDate.Value cell in the DocumentSheet. However, I soon noticed that displayed values from the ShapeSheets and the PageSheet did not always show the value correctly. Indeed, the shape is selected in the following screenshot, and the Shape Data window shows that the value is the 11 Oct. 21, but the group shape display 5/10/2021 (UK short date format), and the sub-shape displays 18 Oct. 21! If I move the shape, then the sub-shape display automatically updates to show the correct date.
So, there has to be a way of ensuring that the correct values are displayed. I found two, one is probably the official way, and the other is my workaround.
The Official Way
The official way, if you study the SETATREF(…) function guide, is to use SETATREFEVAL(…) and SETATREFEXPR(…) functions the second argument. This forces the cell to be evaluated before assigning the value to the target cell, so the formula becomes:
=SETATREF(TheDoc!Prop.StartDate,SETATREFEVAL(SETATREFEXPR()))
However, this does force the value to become local, as can be seen by the blue colour of the formula in the Prop.StartDate.Value cell in the following screenshot:
The Workaround
Another option, which I have used, is to force the cell to re-inherit the formula from the master when it is dropped onto the page. This can be achieved with the following formula in the EventDrop cell:
=IF(STRSAME(LEFT(MASTERNAME(),1),"<"),0,SETF(GetRef(Prop.StartDate),"="))
The purpose of the IF() part is to only act when the shape is dropped on the page, not when the master edit window is open, otherwise pushing the “=” formula into the Prop.StartDate.Value cell will force it to re-inherit from the master shape cell. (Note that the .Value cell is the default, so it does not need to be specified).
Notice that the Value cell is still black, which means that it is inherited from the master shape cell:
Post script
I still feel that it is wrong for Visio to not update the display to match the actual value in a cell, and especially as the documentation says that SETATREF(…) does support the Shape Data rows.
My new book on Visualizing Processes with Microsoft Visio has launched
Back in the early 1990s, there was an application called ABC Flowcharter that was the market leader for diagramming business flowcharts, but some of the brains behind Aldus PageMaker saw an opportunity to create something smarter, and left to write the Visio product, with the stated aim to overtake ABC Flowcharter within 2 years. They…
Interactive Demographics of the European Union in Visio
I was recently contacted by a reader, Stanley M. Max (Towson University lecturer), who had started creating a Visio map of the 27 countries in the European Union in 2020, along with the demographics culled from Wikipedia. He wanted to know if the map and stats could be combined to make a more appealing presentation.…
Understanding Visio Data Graphic’s Color by Value
The desktop Visio editions, Visio Professional and Visio Plan 2, have a great way of automatically displaying data as Data Bars, Icon Sets, Text Callouts and Color by Value. The first three types of Data Graphics require sub-shapes to be inserted into each shape that they are applied to, but the last one, Color by…
Pushing Data Visualizer in Visio beyond its limits
My last post was about some of the lessons learnt when trying to push Data Visualizer to its limits, but this one has some ways of overcoming these limitations. The main lesson learnt is that DV binds the shapes within the DV container shape, CFF Data Visualizer, and controls some of the ShapeSheet cells that…
Pushing Data Visualizer in Visio to the limits!
Regular readers of my blog will know that I like to use the Data Visualizer (DV) in Visio Plan 2, but I recently tried to help a user who really decided to push it to the limits. In this scenario, there were multiple connections, but with different labels, being created between the same flowchart shapes,…
Setting Theme defaults in Visio
I was recently asked how to change the default font size and line weight in Visio, and then saw then many others are asking the same sort of question. I found one reasonable answer suggesting that you should create a new document from your required template, then edit the Styles to suit, and then save…
Leave a Reply