### Author Topic: How to index into a color table?  (Read 1070 times)

0 Members and 1 Guest are viewing this topic.

#### chelmite

• Jr. Member
• Posts: 27
##### How to index into a color table?
« 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?

#### metuemre

• Full Member
• Posts: 174
##### Re: How to index into a color table?
« Reply #1 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)"))

#### Yacine

• Hero Member
• Posts: 2711
##### Re: How to index into a color table?
« Reply #2 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.
Yacine

#### Surrogate

• Hero Member
• Posts: 1030
##### Re: How to index into a color table?
« Reply #3 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)")))``

#### Yacine

• Hero Member
• Posts: 2711
##### Re: How to index into a color table?
« Reply #4 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...
Yacine

#### chelmite

• Jr. Member
• Posts: 27
##### Re: How to index into a color table?
« Reply #5 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!

#### Surrogate

• Hero Member
• Posts: 1030
##### Re: How to index into a color table?
« Reply #6 on: August 14, 2018, 10:24:19 PM »
Why you don't want use advice with SETF function ?

 User.Selector = SETF(GetRef(LineColor),"="&TRIM(INDEX(LOOKUP(Prop.wire,Prop.wire.Format),TheDoc!User.CableColor)))

#### chelmite

• Jr. Member
• Posts: 27
##### Re: How to index into a color table?
« Reply #7 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.

#### wapperdude

• Global Moderator
• Hero Member
• Posts: 3829
• Ideas Visio-lized into solutions
##### Re: How to index into a color table?
« Reply #8 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
Visio 2019 Pro

#### Surrogate

• Hero Member
• Posts: 1030
##### Re: How to index into a color table?
« Reply #9 on: August 15, 2018, 01:32:02 AM »

#### chelmite

• Jr. Member
• Posts: 27
##### Re: How to index into a color table?
« Reply #10 on: August 16, 2018, 04:39:56 PM »
Surrogate, is User.Selector a pre-defined name, or an arbitrary name?

#### Surrogate

• Hero Member
• Posts: 1030
##### Re: How to index into a color table?
« Reply #11 on: August 17, 2018, 02:38:11 AM »
arbitrary, I create new row in user defined cells section.