Shape sheet cell refernence, VBA help needed.

[redwardsjr]

How do I write a statement that will evaluate the 5 possible values in my
field named prop.PRIORITY and change the value of the field named
FillForegnd. I want to change the color of this shape based on the value of
prop.PRIORITY which is linked to a database. The 5 possible values for
prop.PRIORTY are:

A-ASAP / Sever Impact
B-Short Term / Hight Impact
C-Long Term / Mod. Impact
D-None / Low Impact
E-Inaccessible

In FillForegnd I have tried using the function:
=IF(Prop.PRIORITY="C-Long Term / Mod. Impact",5,2)
It seems to always return the true value.

From what I've learned, it seems I need to write an If..Then..Else or an
Select Case statement in a macro or a VB module, but I don't know how to do
it. As you can tell, I'm not a programmer and have never used macros or the
VB editor before. Is there a tutorial anywhere in the MSDN web on writing
simple statements and functions? Thanks for any help.

 

[Markus Breugst]

Hello,

if you want to compare strings, you have to use the STRSAME function. Please
try this one:
IF(STRSAME(Prop.PRIORITY,"C-Long Term / Mod. Impact"),5,2)

In order to check 5 different values, you can interlace the IF statements by
putting an inner IF into the ELSE part of the outer one.
Example:
IF(STRSAME(Prop.PRIORITY,"C-Long Term / Mod.
Impact"),5,IF(STRSAME(Prop.PRIORITY,"B-Short Term / Hight Impact"),4,2))

Best regards,
Markus

 

[John Marshall, MVP]

The following is from http://www.mvps.org/visio/ShapeSheet.htm called
"Colour based on Status" by Mark Nelson

Put this formula in the FillForegnd cell.

=Lookup(Prop.Status,"Black;White;Red;Green;Blue;Yellow")

(A formula error will occur if the custom property, in this case Status,
does not already exist.) Lookup will match the value of the custom property
Status to the list shown above. The value returned by Lookup will be a
number starting with 0 which corresponds to the position of the matched
value in the list. The list shows Visio's numbering sequence for the
colours.
If the property choices are "Warning", "Caution" and "Clear" with
corresponding colours. In the Lookup formula, replace the words "Red",
"Yellow" and "Green" with "Warning", "Caution" and "Clear". You must leave
the other items in as place holders to get the correct colour position. The
final formula would be:

=Lookup(Prop.Status,"Black;White;Warning;Clear;Blue;Caution")

--
John... Visio MVP

Need stencils or ideas? http://www.mvps.org/visio/3rdparty.htm
Need VBA examples? http://www.mvps.org/visio/VBA.htm
Common Visio Questions http://www.mvps.org/visio/common_questions.htm