Solution for better globalizing Visio shapesheet-based solutions!

[Visisthebest]

In an earlier post I asked about why I couldn't get Visio Guy's 'selecting colors with shape data' demo to work on my PC:

http://www.visguy.com/2009/10/21/choose-colors-with-shape-data-fields/

What the problem was: when I changed the color setting(s) in the various shape data dropdowns on the shapes in this demo, the color fill of that shape would simply change to black instead of the color I chose. (what happens in Visio is that the formula simply resolves to the number 0 instead of a RGB(R,G,B) formula as it should (I guess because of an error).

Fortunately I found the reason and the solution was posted under this Visio Guy article:

http://www.visguy.com/2010/04/21/list-separators-in-visios-shapesheet-are-crazy/

Stefan says

JULY 24, 2011 AT 1:01 PM

Thanks for this background information, it was very useful when I had to develop a solution that works in Germany and the UK.

In the end I found that using the LISTSEP() function allowed me to get round the problem of different regional settings:

If a string holding the RGB function is stored in a user-defined cell, like

User.RGBString = "RGB(11,22,33)"

then the following worked (under both UK and German regional settings) for setting the fill color of a shape:

FillForegnd = SUBSTITUTE(User.RGBString,",",LISTSEP())

So the problem is with local settings on some European PC's, and the solution Stefan proposes solves it structurally for any local settings as far as I can see.

There may be other Windows local settings issues, but this one is important too: if you don't fix this for solutions that use shape data to set RGB(R,G,B) color formulas to change shape colors (a very useful thing indeed), your solution won't work in some countries!