Formatting org chart shapes based on data attributes

[stephenf15]

Hi Visio Guy community,

I'm creating an org chart in Visio 2010 from an Excel spreadsheet which is being updated frequently.

I am looking to format the org chart shapes automatically based on their shape data so that the process of reformatting the entire chart manually isn't required every time I have to update the chart to incorporate a minor change in the Excel source file.

Specifically I would like to make the shape outline one of a few specified colours based on one of the shape's data values and the shape fill colour to vary depending on a different shape data attribute.

I have zero experience in Visual Basic beyond switching on Developer mode and having a quick look.  Apologies if this is very simple but I have been unable to find a suitable article online to get me started.

Any help you can provide would be greatly appreciated.

Many thanks,

Stephen

[stephenf15]

So I'm a little bit further along with this for better or worse...

I have made the following line colour formatting work for a single shape under LineColor and I'm happy with it:

=IF(Prop.Prioritisation="Core",RGB(0,176,80), IF(Prop.Prioritisation="Priority",RGB(0,176,240),THEME("LineColor")))

Can anyone advise me on how this can be applied to all Shapes?  Otherwise again I'm doing a manual copy of this formula in all shapes which would be slower than clicking Green border on the standard ribbon!:-)

Thanks,

Stephen

[stephenf15]

Actually I've just copied this to another shape's shapesheet and it is default colouring the cell outline Green regardless of the prioritisation value, ie I haven't gotten it to work and I'm still looking for a full solution.

Thanks,

Stephen

[Nikolay]

I would say it'll take more than 5-minutes to achieve that.

You could start by taking a look at this article by David Parker:
http://blog.bvisual.net/2012/05/08/creating-a-custom-org-chart-template-with-extra-properties/

it explains how to add custom properties to masters for the org chart.
Basically, you need to edit not shapes themselves, but masters upon which they are based, in the document stencil.

[wapperdude]

Also, your formula for comparison should use strsame fcn, e.g., strsame(prop.xyz,"Abc").

Wapperdude

[stephenf15]

Thanks both for the input.  I'll have a look at the article linked and try from there.  If there is any specific steer on using master shapes to control colours via formula I'd appreciate a link to that material or some advice too.

Thanks,

Stephen

[stephenf15]

I've jumped into the master shape/document stencil for the Exec visio org shape (per the article above) but am struggling to construct a suitable formula to make the formatting work.

I would like to turn the LineColor Green if the shape has a Prioritisation value of "Core".

At present, in the Exec stencil-LineFormat-LineColor cell I'm trying to use: =IF(STRSAME(Prop.Prioritisation,"Core"),RGB(0,176,80)) but Visio is rejecting this as it contains an error.  Can anyone advise what my error is and what formula I should be using?

Once this is working I would also like to have the shape's LineColor turn Blue if the shape has a Prioritisation value of "Priority" or "Rec. add to Priority".  All help appreciated!

Many thanks,

Stephen

[Yacine]

Guess you missed the false case.
If(condition,true_formula,false_formula)
(without having tried it out)

[stephenf15]

Thanks for spotting the false case mistake!  I now have the formula:

=IF(STRSAME(Prop.Prioritisation,"Core"),RGB(0,176,80),IF(STRSAME(Prop.Prioritisation,"Priority"),RGB(0,80,176),IF(STRSAME(Prop.Prioritisation,"Rec. add to Priority"),RGB(0,80,176),RGB(0,0,0))))

And it is working for the master shapes which I add it to.

Unfortunately, when I import my data via the Org Chart Wizard, the shapes are imported as individual mastershapes or at least they are not affected by the master shape changes I have made.

Can anyone advise how I can define which master shapes data will be imported into from the org chart wizard?  Or how to use the wizard to import into a saved template?

I followed the directions of the article above to save the updated master shapes as a new template (as best as I could) but no instructions were provided as to how to then use the saved template in conjunction with the org chart wizard.

Stephen

[Nikolay]

First, you prepare the template (modify the shapes); then use "Import" button to import the data from Excel.

The built-in orgchart wizard does not support update,
But you can set up synchronization up to some extent; to achieve this you can use "data connection". I've made a short Video on how:
https://www.youtube.com/watch?v=YnFX1T0aq90&hd=1

There is also a third-party org chart addin (commercial) which addresses some issues of the built-in addin wizard:
http://orgchartforvisio.com/

You might be also interested in reading this: OrgChart Wizard Sucks:
http://visguy.com/vgforum/index.php?topic=837.0

[stephenf15]

Nikolay! What a spectacular help! Thank you!

I have managed to get my Line Color to work and additionally updated my Shape Backgrnd Fill with:

=THEMEGUARD(IF(STRSAME(Prop.ocwiz_1,"Fixed Format"),RGB(255,232,66),IF(STRSAME(Prop.ocwiz_1,"Dashboard"),RGB(170,198,218),IF(STRSAME(Prop.ocwiz_1,"Analytical"),RGB(255,255,255),RGB(239,125,0)))))

The video you created was a very generous use of your time and has helped me to make good progress and will save me lots of time to meet some tight deadlines at my work.

The link to the other thread on this forum has also informed me that auto-sorting sub-positions is not possible which saves me trawling for a solution to that next!:-)

Many many thanks,

Stephen

[stephenf15]

I also can't make use of the data linking functionality covered in the youtube video on Visio 2010 as I don't have that tab but if I can get an updated version on my work laptop I'll definitely be giving this a go!

Stephen