Issue with Colored Lines and Arrows

[anjenaire]

So forgive me as I attempt to stand on the back of giants. I am trying to incorporate two earlier (way earlier) posts to create lines (connectors or as I like to think of them: pipes) via JuneTheSecond (http://visguy.com/vgforum/index.php?topic=1893.0) and VisGuy's blog post (http://visguy.com/vgforum/index.php?topic=1893.0) to color them. Where I am running into difficulty is with the fill of the arrows. I can't seem to get them to match consistently if at all.

I have six types of pipes:
Condenser Inlet
Condenser Outlet
Evaporator Return
Evaporator Supply
Greyed Out
Hot Water Inlet
Hot Water Outlet
White

and corresponding colors:
RGB(0,112,192);RGB(0,176,240);RGB(146,208,80);RGB(120,148,64);RGB(217,149,143);RGB(255,0,0);RGB(191,191,191);RGB(248,248,248)

Just an FYI when I first cobbled this together I had the Hot Water Inlet and Outlet reversed from my required color scheme and that is precisely where I am falling down in the implementation. I had the following shapesheet cells and tried changing them but to no avail:

Original:

=IF(STRSAME("Condenser Inlet",Prop.color1,TRUE),RGB(146,208,80),IF(STRSAME("Condenser Outlet",Prop.color1,TRUE),MSOTINT(THEME("AccentColor2"),-25),IF(STRSAME("Evaporator Return",Prop.color1,TRUE),RGB(0,176,240),IF(STRSAME("Evaporator Supply",Prop.color1,TRUE),RGB(0,112,192),IF(STRSAME("Hot Water Inlet",Prop.color1,TRUE),RGB(217,149,143),IF(STRSAME("Hot Water Outlet",Prop.color1,TRUE),MSOTINT(THEME("AccentColor"),40),IF(STRSAME("Greyed Out",Prop.color1,TRUE),MSOTINT(RGB(255,255,255),-25),IF(STRSAME("White ",Prop.color1,TRUE),RGB(248,2485,248),1.5))))))))


User.colorValues
="RGB(0,112,192);RGB(0,176,240);RGB(146,208,80);RGB(120,148,64);RGB(217,149,143);RGB(255,0,0);RGB(191,191,191);RGB(248,248,248)"

User.PipeType
="Evaporator Supply;Evaporator Return;Condenser Inlet;Condenser Outlet;Hot Water Inlet;Hot Water Outlet;Greyed Out;White"

=THEMEGUARD(IF(CELLISTHEMED(FALSE),SETATREFEXPR(THEMEGUARD(IF(User.visDGCBVFill<>1.5,User.visDGCBVFill,IF(CELLISTHEMED(FALSE),THEME("LineColor"),THEMEGUARD(IF(CELLISTHEMED(FALSE),SETATREFEXPR(THEMEGUARD(User.color1)),SETATREFEXPR(THEMEGUARD(User.color1)))))))),SETATREFEXPR(THEMEGUARD(IF(User.visDGCBVFill<>1.5,User.visDGCBVFill,IF(CELLISTHEMED(FALSE),THEME("LineColor"),THEMEGUARD(IF(CELLISTHEMED(FALSE),SETATREFEXPR(THEMEGUARD(User.color1)),SETATREFEXPR(THEMEGUARD(User.color1))))))))))

Changed:

=IF(STRSAME("Condenser Inlet",Prop.color1,TRUE),RGB(146,208,80),IF(STRSAME("Condenser Outlet",Prop.color1,TRUE),RGB(120,148,64),IF(STRSAME("Evaporator Return",Prop.color1,TRUE),RGB(0,176,240),IF(STRSAME("Evaporator Supply",Prop.color1,TRUE),RGB(0,112,192),IF(STRSAME("Greyed Out",Prop.color1,TRUE),RGB(191,191,191),IF(STRSAME("Hot Water Inlet",Prop.color1,TRUE),RGB(217,149,1430),IF(STRSAME("Hot Water Outlet",Prop.color1,TRUE),RGB(255,0,0),IF(STRSAME("White ",Prop.color1,TRUE),RGB(248,248,248),1.5))))))))

User.colorValues
="RGB(0,112,192);RGB(0,176,240);RGB(146,208,80);RGB(120,148,64);RGB(217,149,143);RGB(255,0,0);RGB(191,191,191);RGB(248,248,248)"

User.PipeType
="Condenser Inlet;Condenser Outlet;Evaporator Supply;Evaporator Return;Greyed Out;Hot Water Inlet;Hot Water Outlet;White"

Anyone with some pointers in the right direction for me? I know this should be simple but I obviously don't understand enough about the shapesheets to make it work correctly.

[vojo]

For formulas this complex, I tend to break them up into multiple user cells
For example, instead of have the color as a big look up array, use a user.cell to actually hold the result of lookup, then
use another cell to take that result to make the next choice.

I have seen complex shapes where somebody has use 100 user cells just to be methodical and be able to debug
misbehavior.

That said, you may want to start of with no themes....get it the way you want...then try to fold in themes
(you at least can figure out if its work to date or what you did in themes is messing you up).  I don't know for sure
but I think themes mess with the underlying color (HUE, etc) vs sometimes outright changing color.

Note, if any of this is 3D, keep in mind that lighting defaults warm matte....so 3D slow slopes will have
a gradient of color.   Need to select the last icon in top row to make flat gradient (solid color).

Also, FWIW, your eye picks up regions better than lines.   So if got one of these cool displays, you may need to calibrate
the color to make sure you are getting what you want.  You can test this by making the line weight heavy so its actuall a region and see if you notice the difference between line color and fill color.   You may need to mess with the *.icc files in windows to get a good color mix  (MS has a sense of humor regarding colors on windows 10  ;-)   )

[Yacine]

Hi Anjenaire,
the way you set up the color switch is very complicated. You check for an input and decide by means of IF clauses which color to chose.
The trick is actually to bind the color value to the input by means of an index (sort order) of a semi-colon list.
You began right, but somehow everything got complicated afterwards.

A little problem to overcome: the colors are listed as strings, so you can't reference them directly. Visio reads a string and return 0 for the value.
The solution is to write the value in the intended field by means of SETF(GetRef(TheCell), ... and to make sure that the string is really meant as formula add a "=" sign in front of the value. So for the line color for example you'd write: SETF(GetRef(linecolor),"=" & user.color1).

The arrow being a subshape of the connector, you would first get the color from its parent. eg: user.color1 = sheet.4!user.color1.
Then the same SETF/GetRef commands to color it.

HTH,
Y.

check the attachment.

[anjenaire]

@Yancine Thanks for the insight. I was following two different shape examples and tried to mash them together. Didn't work so well. I still see I have the issue with the two hot water connector arrows being the opposite colors than what I am hoping to achieve. How do I access that subshape or where does it get defined on the shapesheet?

Thanks again.

[Yacine]

They shouldn't be an opposite color any more, as they now reference the same value.
What I observed however is that you are playing with transparency as well.
That may be the confusing point. Delete the transparency formulas.

Where the arrow can be edited? Open the drawing explorer, browse the different branches until you find the arrow. Once selected, open its shapesheet.

Rgds,
Y.

[anjenaire]

I am heading in the opposite direction. The two heating pipe arrows were opposite of my preferred color scheme so I went back to the original shape but now I can't seem to make the "arrow" any color except black. What am I missing here?

So close and yet so far.

[wapperdude]

In your attached VSD file, the shapesheet User section is missing some critical rows for the black pipes.

Wapperdude

[Yacine]

I am heading in the opposite direction. The two heating pipe arrows were opposite of my preferred color scheme so I went back to the original shape but now I can't seem to make the "arrow" any color except black. What am I missing here?

So close and yet so far.

You did not follow my advice.
In my example, I showed that the arrow needs to get its color from its parent shape.
You did not implement this function in your arrow, it has now rather its own color switch mechanism.

[anjenaire]

The arrow being a subshape of the connector, you would first get the color from its parent. eg: user.color1 = sheet.4!user.color1.
Then the same SETF/GetRef commands to color it.

After a night's sleep I came at it this morning with fresh eyes and did implement your advice and all is well. I wasn't understanding the direction, at first, but now I understand that the subshape sheet's user.color1 gets it color by referencing the main shape's shapesheet (sheet.5 in my case) using the sheet5!user.color1 and the SETF/GetRef Command.

Thanks for all the assistance.