A recent question on the VisioGuy forum asked how you would go about creating a Visio shape that displays multiple colours based on linked data within an Excel workbook. I thought I’d have a look at how you might tackle this problem…
I’m going to spilt this post into three parts. I’ll start by looking at what options are available to this type of problem. In the second post I’ll create a walkthrough of the proposed solution and in the third part I’ll discuss what’s required to maintain it.
Exploring options
Any shape that needs to display multiple colour options requires some kind of array, list or container of one type or another to hold the possible colour values. Whatever object you use, you usually want a single source so that the values can be changed later on.
Colour arrays - When I built the jQuery Mobile shapes I opted for RGB array strings held in User cells in the Document ShapeSheet. This works reasonably well, but it also presents problems around language compatibility. (Specifically the argument delimiter character used in the RGB function when kept in string format.)
User Cells - Avoiding RGB strings then, another option might be to separate out the colour palette into individual User cells and then have a calculated value cell based on your own logic. For example:
Where the User.CalculatedColor cell looks like this:
That’s not a bad solution, although it still leaves you with having to edit multiple (User.ColorX) cells when you want to change the palette and the ShapeSheet isn’t the most user friendly place for everyone.
ThemeColors - I then had a thought about using ThemeColors as a palette holder. This has the benefit of a user friendly dialog and named colours (ie FillColor, Accent1, Accent2, Accent3 etc.) You can create a custom ThemeColor via Design [tab] / Themes / Colors and name it accordingly. For example, I created a ThemeColor named ConditionalColors and if you look through the Drawing Explorer, this creates a hidden master shape with the same name.
Although the Visio UI only allows you to apply themes at page level, you can also set a shape level theme by editing the User.msvThemeColor cell to use a separate theme source. (This also applies to User.msvThemeEffects.) It is really just a pointer to the theme master in the case of custom themes.
That’s great, but there’s a problem with this approach and that’s how Visio refers to those custom themes – it uses GUID’s (globally unique identifier). Since the formula in the User.msvThemeColor cell uses the USE ShapeSheet function, you might think, as I did, that you can use the master name as the documentation describes (to be fair it only talks about the USE function in association with line pattern, fill patterns and line ends). In the case of themes however, only a GUID will do.
So the following will not work even though the master exists:
User.msvThemeColor = USE("ConditionalColors")
but this will:
User.msvThemeColor = USE({00BA13A2-0002-0000-8E40-00608CF305B2})
I think that’s a shame as, if you’re trying to create a code free solution, then GUIDs are not a very robust tool, at least not for human eyes.
Data Graphics - I was chatting to David Parker about all of this and he suggested I look at the Color by value (CBV) – part of Visio’s Data Graphics functionality. By default CBV sets a single colour on a single shape based on a set of user-defined criteria. There are a couple of ways around trying to read in multiple shapes (and therefore colours) and I’m going to describe one of them in the second part of this post, but the CBV certainly looks like a good approach for this problem. Not only does it provide a user-friendly dialog from the colour setting perspective but it also gives you conditional criteria building tools removing the need to create complex logic in the ShapeSheet.
Note that Data Graphics are only available in the Professional edition of Visio 2007 and Professional and Premium in 2010 so if this isn’t available to you then you’ll have to adopt ‘User Cells’ route.
