What is the formula to change line properties from custom property

[Anderson]

am using the Visio 2007 Pro edition:
I would like to change the Line Pattern for an object based on the contents
of a custom properties cell.

I created a custom Shape Data property call "Message_Exchange_Method". It
has three values (Manual, Automatic, Unknown) and I want to change the line
pattern based on the value.

The closest function I could find was the "IF" function even though it
doesn't really have an ifelse type option.
When I tried using the "IF" statement, it would always evaluate to "true".

IF((Prop.Message_Exchange_Method)="Manual",23,1)

I saw the thread in this group named: IF()Functions and strings and then
tried the following:
STRSAME(Prop.Message_Exchange_Method,"Manual",23). But this did not
evaluate as expected and limits the values that may be listed in the cell
contents for Message_Exchange_Method.


I have successfully used functions to get cell contents with "Text Field" (
=(Prop.ICD_No)&CHAR(10)&(Prop.DataType1)&CHAR(10)&(Prop.DataType2) )

Do you have any suggestions for how I can make the line pattern change based
on contents of another cell?

 

[John]

Hi Anderson,

Have a go with the following (which uses an extra User cell):

Prop.Message_Exchange_Method.Format cell = "Manual; Automatic; Unknown"

User.MsgExchMeth cell
=LOOKUP(Prop.Message_Exchange_Method,Prop.Message_Exchange_Method.Format)

Line format cell =IF(User.MsgExchMeth=0,23,1)

The Lookup function returns the index of of the selected item (zero based),
so Manual=0, Automatic=1 and Unknown=2.

Let me know how you get on.

Best regards

John

 

[Anderson]

Thank you for responding.
I am working with the shapesheet for the object. When I tried to enter the
name "Prop.Message_Exchange_Method.Format" as a Shape Data property I
recieved an error. The error implied a decimal was not allowed in the
property name. I changed the name to "Prop.Message_Exchange_Method_Format"
and updated the Lookup formula.

However, the value returned for User.MsgExchMeth does not change. It
returns a -1.

any thoughts on what I did incorrectly?

 

[David Parker]

Format is a particilar cell in a row in the Custom Properties/Shape Data
section
I think John assumed that you had entered Type =1 and had the value "Manual;
Automatic; Unknown" in a Row named Message_Exchange_Method
(The Prop. gets added automatically)
Then User.MsgExchMeth =
LOOKUP(Prop.Message_Exchange_Method,Prop.Message_Exchange_Method.Format)
would work whenever you selected a value in Prop.Message_Exchange_Method
You could have the following in LinePattern =GUARD(INDEX(User.MsgExchMeth
,"1;2;3"))
Alternatively, you could have another user cell with the formula:
=SETF(GETREF(LinePattern),"=GUARD(" & INDEX(User.MsgExchMeth ,"1;2;3") &
")")

 

[Anderson]

User.MsgExchMeth is still evaluating to -1.

User.MsgExchMeth=LOOKUP(Prop.Message_Exchange_Method,Prop.Message_Exch_Method_Options,";")

Prop.Message_Exch_Method_Options="A2A; BizTalk; Cron Job; PJS; Pt2Pt; SQL;
Manual; Export/Import"
Prop.Message_Exchange_Method="PJS"

Format
=FIELDPICTURE(0)=(Prop.DataType2)&CHAR(10)&(User.MsgExchMeth)

It displays
PartNumber
-1

It doesn't matter what value (ie.. A2A, Biztalk,...) I set
Prop.Message_Exchange_Method, I it still only evaluates to a -1.

I tried various permutations of what you sent and did not get anything to
change the value.

 

[David Parker]

You have a space after each semi-colon in the Format cell. You shouldn't
have.

 

[John]

Your formulas work for me. Are you sure there's no space after the PJS
value in the Prop.Message_Exchange_Method cell?

Best regards

John

 

[Anderson]

I am using Visio 2007 (12.0.4518.1014) MSO (12.6017.5000)

It appears to be the lookup command.

I tried the example command for lookup
Example 1
LOOKUP("rat","cat;rat;;goat")

Returns 1.


The lookup command returned a 1 as in the example. However, it continues to
return a -1 no matter what I use as values in the properties values.
I changed the values of
prop.message_exch_meth_options to be "cat;rat;;goat"
and prop.message_exch_method to be "rat"

still get -1

 

[John]

Hi there,

yes I'm 2007 as well. My guess is you've just got the format wrong
somewhere although it's difficult to know with seeing the shapesheet.

Take a look at this blog
(http://www.visguy.com/2006/11/22/consumer-reports-rating-shape/) from Chris
Roth in which he talks about the Lookup function. He's got a file download
at the end of the post so you can see what's happening.

Let us know how you get on.

Best regards

John

 

[John Marshall, MVP]

David is on the right track.

If you have a list of items, then you should use a list (The Custom
Property/Shape Data Type should be 1). As you mention, STRSAME, is a pain to
use. So once you have a Custom Property that is a list, you should add a
User cell that converts the text to a number, preferablly the index into the
list.

To do this, create User cell named MsgExchMeth with a value of
= LOOKUP(Prop.Message_Exchange_Method,Prop.Message_Exchange_Method.Format)

Anywhere in the shapesheet you need to check the value of the custom
property, you only need to check the value of the User cell. This gives you
the possibility of looking for specific patterns; the first three entries
"x<3", the even entries "Mod(x,2)=0". etc.

One big benefit is that as long as you do not change the order, you can
change the content of the list in the Format cell of the custom property
without changing any other code. For example, if the list contained Y; N,
you could easily change the list to Oui; Non or Yes; No without having to
search for everywhere the custom property is used (the Formula Tracing
window will help find these references) and then change it.

John... Visio MVP

Need stencils or ideas? http://visio.mvps.org/3rdparty.htm
Need VBA examples? http://visio.mvps.org/VBA.htm
Visio Wishlist http://visio.mvps.org/wish_list.htm