Visio Guy

Visio Discussions => General Visio => Topic started by: chelmite on August 14, 2018, 03:48:47 AM

Title: How to index into a color table?
Post by: chelmite on August 14, 2018, 03:48:47 AM
I am trying to change the color of a connector based on a Shape Data value:

"WireType" has
Format: "Double Smurf;Cable;Internet;Telephone;Camera;Security"
Value: INDEX(0,Prop.WireType.Format)

I tried setting LineFormat.LineColor to THEMEGUARD(INDEX(Prop.WireType,"rgb(98,205,242);rgb(192,0,0),rgb(0,112,192),rgb(0,0,0),rgb(0,192,0),rgb(0,0,192)"))
thinking that I could use Prop.WireType as an index into these rgb values.

Any idea on how to get this to work?
Title: Re: How to index into a color table?
Post by: metuemre on August 14, 2018, 08:51:38 AM
Hi chelmite,

Try this formula, =THEMEGUARD(INDEX(LOOKUP(Prop.WireType,Prop.WireType.Format),"rgb(98,205,242);rgb(192,0,0);rgb(0,112,192);rgb(0,0,0);rgb(0,192,0);rgb(0,0,192)"))
Title: Re: How to index into a color table?
Post by: Yacine on August 14, 2018, 03:27:57 PM
@metuemre, I tried your approach, but somehow the cell won't interpret it as valid rgb formula.
What worked however, was a setf/getref assignment to the target cell.
Title: Re: How to index into a color table?
Post by: Surrogate on August 14, 2018, 03:54:09 PM
I aggry with Yacine and try add user-defined cell with formula
Code
SETF(GetRef(LineColor),TRIM(INDEX(LOOKUP(Prop.WireType,Prop.WireType.Format),"RGB(98,205,242);RGB(192,0,0);RGB(0,112,192);RGB(0,0,0);RGB(0,192,0);RGB(0,0,192)")))
Title: Re: How to index into a color table?
Post by: Yacine on August 14, 2018, 04:29:28 PM
To add to Surrogate's explanation. My direct assignement "RGB(..." only worked when I explicitely told Visio to intrepret the assignment as formula by means of an equal sign at the beginning.
eg: SETF(GetRef(LineColor), "=" & TRIM(INDEX(LO...
Title: Re: How to index into a color table?
Post by: chelmite on August 14, 2018, 08:19:16 PM
I tried moving the type list and color list to the document:
User.WireTypes="Cable;Camera;Double Smurf;Internet;Security;Telephone"
User.CableColors="rgb(192,0,0); rgb(245,157,86); rgb(98,205,242); rgb(0,112,192); rgb(224,0,0); rgb(0,0,0)"

Then, each connector has:
ShapeData.Prop.WireType.Format=TheDoc!User.WireTypes
ShapeData.Prop.WireType.Value=INDEX(3,Prop.WireType.Format)
LineFormat.LineColor=THEMEGUARD(INDEX(LOOKUP(Prop.WireType,Prop.WireType.Format),TheDoc!User.CableColors))

This works...for a single connector.

How do I create more connectors with the same fields? I tried copying the connector. The "duplicate's Shape Data has
the text "=TheDoc!User.WireTypes".
Looking inside the shapesheet, the duplicate has:
ShapeData.Prop.WireType.Format="=TheDoc!User.WireTypes"
ShapeData.Prop.WireType.Value=INDEX(-1,Prop.WireType.Format)
LineFormat.LineColor=RGB(192,0,0)

I tried wrapping
ShapeData.Prop.WireType.Format=GUARD(TheDoc!User.WireTypes)
LineFormat.LineColor=GUARD(THEMEGUARD(INDEX(LOOKUP(Prop.WireType,Prop.WireType.Format),TheDoc!User.CableColors)))
but, the duplicate ended up with the same result as without GUARD.

Any ideas on how to fix this?
Thanks!
Title: Re: How to index into a color table?
Post by: Surrogate on August 14, 2018, 10:24:19 PM
Why you don't want use advice with SETF function (https://docs.microsoft.com/en-us/office/client-developer/visio/setf-function) ?

User.Selector= SETF(GetRef(LineColor),"="&TRIM(INDEX(LOOKUP(Prop.wire,Prop.wire.Format),TheDoc!User.CableColor)))
Title: Re: How to index into a color table?
Post by: chelmite on August 14, 2018, 10:46:03 PM
I tried entering that in the LineColor cell, but got a circular reference error. I think your code might work in a script somewhere, but I'm not running a script.

I then trimmed it down to: =TRIM(INDEX(LOOKUP(Prop.WireType,Prop.WireType.Format),TheDoc!User.CableColors))
That calculates the correct value.
However, when I copy and paste the connector shape, the fields in the new shape are changed, as I described before.
Title: Re: How to index into a color table?
Post by: wapperdude on August 15, 2018, 12:11:30 AM
The SETF formula cannot go into the cell that it is referencing.  As provided, it was placed in User.Selector cell.

@Yacine, @Surrogate: Curious, as I have V2007, I've not run into the syntax:  LineFormat.LineColor before.  When did that become a legitimate cell reference format.  I could not find reference to that anywhere associated with Visio.  Not even in the object model.  Just wondering.

@chelmite:  In V2007, the working syntax is below. Replace Prop.R1 with your Prop.WireType

 
Code
    vsoShp.CellsU("LineColor").Formula = "INDEX(LOOKUP(Prop.R1,Prop.R1.Format), " & Chr(34) & "RGB(0,0,0;RGB(255,0,0);RGB(0,255,0);RGB(0,0,255)" & Chr(34) & ")"

Wapperdude
Title: Re: How to index into a color table?
Post by: Surrogate on August 15, 2018, 01:32:02 AM
@wapperdude, I know about this issue in V2007. In the Russian forum was a similar discussion (https://translate.googleusercontent.com/translate_c?act=url&depth=1&hl=en&ie=UTF8&prev=_t&rurl=translate.google.com&sl=ru&sp=nmt4&tl=en&u=http://visio.getbb.ru/viewtopic.php%3Ff%3D5%26t%3D844%26st%3D0%26sk%3Dt%26sd%3Da%26sid%3Dbebcbd7824022f70a853d2206968a7e1&xid=25657,15700021,15700124,15700149,15700168,15700186,15700190,15700201&usg=ALkJrhgSjvItxTkU-W_TEvLHnDULxfmjrA)
Title: Re: How to index into a color table?
Post by: chelmite on August 16, 2018, 04:39:56 PM
Surrogate, is User.Selector a pre-defined name, or an arbitrary name?
Title: Re: How to index into a color table?
Post by: Surrogate on August 17, 2018, 02:38:11 AM
arbitrary, I create new row in user defined cells section.