FillForegnd in Shapesheet using wrong data

Question

I have a work related problem and, as such, I cannot attach the example file.

I use dynamic shapes as part of a diagram, where the user can change the shape data (i.e. change a category) and the shape will change colour. The mechanics for this are in the ShapeSheet.

The key piece of data (Prop.Category) is an index from a fixed list of options. A User field (User.ForeColour) uses the Prop.Category data to determine the RGB colour value from another fixed list (another index/lookup formula). I have FillForegnd set to access User.ForeColour directly.

In my example here, the user selects Prop.Category #3, which indirectly sets User.ForeColour to RGB(128,0,128). However, in my current file setup, the FillForegnd is then set to RGB(64,86,150). When I select other categories the FillForegnd values are also changed from the expected value.

I have checked the ShapeSheet using both the formula and values view. Everything is correct (except for the final value in FillForegnd). Double checking User.ForeColour shows that it holds the value RGB(128,0,128) as expected.

Interestingly, if I overwrite the formula in User.ForeColour with the static value RGB(128,0,128), FillForegnd is correctly populated and the shape shows the correct colour. Return to the formula (which still ends up at the right value) and the FillForegnd is again populated with the wrong RGB value.

I have checked and removed themes, this has had no effect.

Visio 2010, Networked Windows 7 environment.

I have tried to duplicate the issue in a new file on a different system (so that I could get an uploadable example), but was not successful. The file with the original master shape still works as expected. The new file with the copied shapes (and by default, the copied master) is the one with problems. I have double checked the ShapeSheet and the default/master settings are as expected (black text) and the tailored choices (blue text) are as expected.

Answer 1

The good folk at the visguy forums provided enough insight to solve the question I have posed here.

The User.Forecolour property used a formula (INDEX(...)) to determine the desired colour value. This use of formula did not sit well with the FillForegnd cell.

Three simple solutions exist. Both solutions use SETF(...) rather than having FillForegnd point to a formula.

1. The User.Forecolour property can use SETF(GetRef(FillForegnd),INDEX(...)). This will force a value into FillForegnd and is the easiest modification to the original code. A downside is any user modifications to the shape itself will overwrite the FillForegnd cell which won't be corrected until the user selects the right shape data again.
2. An additional User.X cell can be created. The SETF in answer 1 would GetRef(User.X) instead. FillForegnd would contain Guard(User.X). This will stop users randomly changing the colours.
3. The third option extends both the previous answers. In User.ForeColour : =SETF(GetRef(FillForegnd),"GUARD(INDEX(...))"). This will set the GUARD as necessary in the foreground colour area.

All of these solutions leverage off the characteristic that SETF can overwrite cells that have a GUARD, but normal user intervention in the GUI (shapes page area) can't.